false application, mm ss, military time: The following code in a macro will do this: ActiveCell.FormulaR1C1 = =NOW() Selection.NumberFormat = h:mm:ss;@ Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False...

Excel: Excel, text color when between 2 values, hobbs meter, excel spreadsheet

hobbs meter, excel spreadsheet, oil change: You didn t specify the version of excel, but it is basically going to be done via the format, conditional formatting route I think. This allows you to set rules based on values or formulas. You can only have 3 levels of formatting, but if you include the...

Excel: Excel VBA to conditionally select formula, cell reference, a45

cell reference, a45, ogilvy: Brian, so for every cell that has a formula that does not have a reference to text, then replace that formula with the value returned select the cells to process and run the macro sub replaceformula() dim cell as range for each cell in selection...

Excel: Excel VBA Formula Problem, excel vba, thanks in advance

excel vba, thanks in advance, a3: Based on the way you are doing this and without suggesting a different way then try this easier to read approach: Dim FirstCell As String Dim SecondCell As String FirstCell = Cells(x, 1).Address _(rowabsolute:=False,_columnabsolute:=False) SecondCell...

Excel: Excel VP Code, target cells, vbyesno

target cells, vbyesno, code option: 2 things come to mind: the cell is protected; you re in a event loop. In the latter case, surround the .Value=Now() with these lines: Application.EnableEvents = False .Value = Now() Application.EnableEvents = True because changing the value to Now will...

Excel: Excel and Word question, word question, word form

word question, word form, aidan: You didn t give me the details of what Damon had provided, but it should be very easy to get Word to communicate with Excel, and should be possible to do it via a button - also no problem - my email is aidan.heritage@virgin.net if it helps to send me sample...

Excel: Excel-Worksheet Change will not trigger after pasting data to the worksheet, target cells, target range

target cells, target range, len value: Rodney, If that is what you were doing when you copied single cells, then it should work. I didn t just put the code up here. I thoroughly tested it in my workbook and it worked perfectly. As far as sheet names - there are no sheet names in the code....

Excel: Excell Formula Functions, ogilvy, ths

ogilvy, ths, many thanks: Russell, assume that the column to check is column F =LOOKUP(9.99999999999999E+307,F:F) don t put the formula in column F (the same column being checked). Ths should give you the last number placed in the column being checked. It worked for me....

Excel: Extract Data to New Sheet, de bruin, autofilter

de bruin, autofilter, excel 5: Gina, If you want to use a macro, Ron de Bruin has written sample code to do what you describe. It can be found here: http://www.rondebruin.nl/copy5.htm that uses the autofilter to identify records for each employee, but since the macro is applying...

Excel: How to enter an Excel formula in a macro without using the string format?, polynomial coefficients, formula index

polynomial coefficients, formula index, excel formula: Hi Kaushalya, You could do something like this: Sub Example() Dim oRange As Range On Error Resume Next Set oRange = Application.InputBox( Please select cells , Select cells for formula , , , , , , 8) If Not oRange Is Nothing Then...

Excel: How to enter a formula in an excel macro without using the string format, polynomial coefficients, range names

polynomial coefficients, range names, cell references: Hi Kaushalya, Use ActiveCell.Formula instead of ActiveCell.FormulaR1C1 to enter a formula with cell references or range names. Example: ActiveCell.Formula = =SUM(A1:A8) Doug Smith Brainbench MVP for MS Excel www.brainbench.com www.abundant-solutions.co...

Excel: excel, trial balance, sheet1

trial balance, sheet1, excel workbook: Crisca I am not sure exactly what the question is but maybe this will help--the easiest way to transfer a total (or any other data) from one sheet to another is to use a linking formula. The formula looks like this =Sheet1!A99 The easiest way to create...

Excel: excel, ones and zeros, countif

ones and zeros, countif, e5000: if the numbers are in one column, then a simple countif would do it for you - this would return (presumably) ones and zeros, so filtering on the zeros would show you the missing values =countif(BigRange,YourTestValue) replace bigrange with the 70,000...

Excel: excel cross work book look up, master lists, explantion

master lists, explantion, spread sheets: The basic formula is vlookup - the only issues I can see are (1) you have potentially a growing list and (2) you mention that the data is entered in an inconsistent format - I would try to standardise the format if at all possible. The caps issue isn t a...

Excel: excel database mgnt, logical tests, common store

logical tests, common store, stock updates: I d probably do this in Access, but excel sould be fine - I think the formula you need for stock is a multiple condition sumif - which can be done with sumproduct =sumproduct(--(range1=test1),--(range2=test2),range3) you can have as many tests as you...

Excel: excel drop down menus, data validation, drop down menus

data validation, drop down menus, drop down list: A drop down list is usually a data validation list, so wouldn t allow anything else to be entered, BUT you could PROBABLY use VBA if you needed to to force the entry to change - personally, I would stick with enforcing the ACTUAL entries - after all, picking...

Excel: excel problem, excel, formula

excel, formula: There are not enough details to craft a formula. From the question, something like: =A1*Sheet2!A2 could work, but I don t think that this is what you are looking for. Could you be more specific on what you are looking for? What are the rules that the...

Excel: excel range to word row 12, insertion point, word macro

insertion point, word macro, text test: I tend not to work with copy and paste, preferring to set the selection text by the use of format etc commands. However, Word can handle bookmarks - if the file is a template then having a bookmark would be sensible 0word.bookmarks( whatever ).range.text=...

Excel: excel spreadsheet, len function, excel spreadsheet

len function, excel spreadsheet, linda linda: Linda, I put in your examples and clicked the button to right align them and I saw no spaces on the right. could you send me a sample workbook that illustrates the problem and I will see what I can discover for you send to twogilvy@msn.com It...

Excel: excell ?, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: To my knowledge, you can t do it without some custom VBA programming or a macro. 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: export/convert multiple text files to excel format, sh int, interface

sh int, interface: alvin cayetano this worked for me: Make the sheet where you want the combined data the activesheet, then run this macro. When prompted, select the multiple files you want to process (in the order you want them processed). this assumes the source...

Excel: extract data from one sheet to another, column labels, radio button

column labels, radio button, column headers: Noshin, the trick in transfering to another sheet using advanced filter is to start with the destination selected. so at the destination, put in the column headers for the columns that you want copied over. So say in the destination sheet in cells...

Excel: File name from a formula, file names, vba

file names, vba, source file: with difficulty- the indirect worksheet function could do it, but it needs the source file open - so the only way I can think of to do this would be via vba, which would set the formula, update the value, and then replace with the value. my email is aidan.heritage@virgin.net...

Excel: File name from a formula, c10, copy paste

c10, copy paste, file names: Assuming the other values are in C9, C10..., you can do this (follow the steps precisely): If the = [1BW108-1.xls]Review !$F$55 is now in cell A1, put this in cell A1 and fill down to A150: = = [ &c8& .xls]Review !F55 (in A2 you should see = = [ &c9& .xls]Review...

Excel: Filter Data From Multiple worksheets, pivot table, cell a1

pivot table, cell a1, sh1: Ahmed, One way would be to copy all the data to a single sheet, then delete the rows you don t want. Assumes all the data is contiguous and starts in cell A1 on each sheet. Sub consolidateData() Dim sh As Worksheet, sh1 As Worksheet Dim r As Range,...

Excel: Filter By Month, top menu bar, mail id

top menu bar, mail id, excel sheet: Hi Antony, In order to filter expenses by month (or a date range) please follow these steps: -Select the columns that have the data you would like to sort -Click on Data on the top menu bar -Select Filter, then AutoFilter -You will see an arrow in each...

Excel: Filtering data, chunk data, paste operation

chunk data, paste operation, excel spreadsheet: Tomás, Try the ADVANCED FILTER. IT has a checkbox that makes the filter COPY the selected values to another area. Unfortunately, it appears that the other area has to be on the same sheet as the original database. So, you get the copied...

Excel: Filtering data excel 2007, unique records, spread sheet

unique records, spread sheet, aidan: Tricky for it to be totally automatic - possibly the quickest and easiest way is to have a countif formula that counts DOWN from current cell to bottom of range and checks the name - you can then filter on all the 1 s which will give you the unique list. ...

Excel: Find reference of a min or max on list of number(between 10~400), spreed sheet, ogilvy

spreed sheet, ogilvy, c2: Assuming A is in A2 and 34.47 is in C2 (just to orient the data with the sample formulas below), then =INDEX(A1:A5,SUMPRODUCT((B2:C5=MIN(B2:C5))*ROW(B2:C5)),1) =INDEX(A1:A5,SUMPRODUCT((B2:C5=MAX(B2:C5))*ROW(B2:C5)),1) gave me the min and the max respectively....

Excel: Find Text - Delete Rows, word summary, column c

word summary, column c, loop through: Greg, That only works because you don t have two rows next to each other that contain summary. If you did, you would skip one of them. In any event, you solve that by looping backwards from the highest numbered row to the lowest numbered row. Dim a...

Excel: First question of the day, ubound, ogilvy

ubound, ogilvy, question of the day: Hunter, A value of zero anywhere in the row Sub DeleteZeroRows() Dim r As Range, r1 As Range Dim cell As Range, v As Variant Dim i As Long v = Array(xlFormulas, xlConstants) Set r1 = Nothing For i = LBound(v) To UBound(v) Set r = Nothing ...

Excel: Flag Cells, page layout view, array formula

page layout view, array formula, page break: That is strange, as it worked well for me. Could you evaluate the formula (menu Tools- Formula Auditing- Evaluate formula) and tell me what is happening? The logic should be: 1. Flag if column J2 (more than 15 above) is TRUE 2. Flag if two conditions happen:...

Excel: Formatting Number in VBA, vba code, cell a1

vba code, cell a1, novice: Here s an example. When the entry in TextBox1 changes, this code puts the result in cell A1, as a number. Private Sub TextBox1_Change() If IsNumeric(TextBox1) Then ThisWorkbook.Sheets(1).Range( a1 ) = TextBox1 + 0 End If End Su...

Excel: Formulário, cula, em portugal

cula, em portugal, convers: Olá, Italo! Como está? Sim Sou Portuguesa e vivo actualmente em Portugal. De que estado do Brasil é? Nunca fui aí, mas tenho muitos amigos Brasileiros :-) Experimente o seguinte, sff: 1. Formate a célula A1 da folha activa para moeda 2. Execute o...

Excel: Formula for Calculating a Due Date, excel formula, ogilvy

excel formula, ogilvy, due date: Matt, A2: 02 Apr 09 B2: =DATE(YEAR(A2),MONTH(A2)+4,0) worked for the two dates you spedified and I would expect it to work for all situations. Don t worry about going across the end of the year - it will work for that as well. -- Regards,...

Excel: Formula help!, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, countif: You can use the SUMIF and COUNTIF functions to add up or count values based on specific criteria, respectively. Here is a free tutorial that explains how to use both: http://www.599cd.com/tips/excel/sumif-countif?key=AllExperts Let me know if you...

Excel: Formula to identify Mondays and add up values for week, header area, sum function

header area, sum function, pivot table: I would do this by creating a helper column which has the formula =a1-weekday(a1)+2 this should translate A1 into a mondays date. You can then use a pivot table to get the summaries you want automatically - you didn t specify version, but other than...

Excel: Formulas in Excel 2007, cell b2, formulas in excel

cell b2, formulas in excel, sheet1: Example: on Sheet1, Col A, you have a list of names in a particular order. On sheet2, you have a database, with the first field of the first record in cell B2, and col B being names from Sheet1, but in a different order. You want to re-order the Sheet2...

Excel: Formulas that include column headings, column headings, cell reference

column headings, cell reference, au2: Russell, I am not sure what the problem is with =NOT(E2=E1) but if it is an issue then the answer to both of your problems is to make an exception =if(row()=2,True,NOT(E2=E1)) row() returns the row that contains the formula. So if it is row...

Excel: auto populate a cell from validation list, questioner

Excel: auto populate a cell from validation list, questioner

Excel: What are bin numbers?, excel help file, days of the week

Excel: What are bin numbers?, excel help file, days of the week

Excel: Change rounding rule of 4/5 to 5/6., negative values, a1

Excel: Change rounding rule of 4/5 to 5/6., negative values, a1

Excel: Changing cell colors depending on the date

Excel: Changing cell colors depending on the date

Excel: Data Validation in Excel with Tables, data validation, table names

Excel: Data Validation in Excel with Tables, data validation, table names, cell a1

Excel: Editing macro for selecting multiple items from a drop down list., dear mr, mr tom

Excel: Editing macro for selecting multiple items from a drop down list., dear mr, mr tom

Excel: Excel

Excel: Excel

Excel: line chart does not start at 0, horizontal axis, tick

Excel: line chart does not start at 0, horizontal axis, tick, good luck

Excel: Need a Formula to Search Upwards, shift key, google

Excel: Need a Formula to Search Upwards, shift key, google

Excel: Sorting data into "bins", array function, bin width

Excel: Sorting data into "bins", array function, bin width, help file

Excel: UserForm Password, hidden password, two answers

Excel: UserForm Password, hidden password, two answers, vba

Excel: filtering by date, drop down menu, arrows

drop down menu, arrows, arrow: Before doing this make sure you have a title for each column 1. Click on the database in the date column 2. Select the Data drop down menu 3. Select Filter 4. Select Autofilter This will put small arrows at the top of each title. Click on the...

Excel: How to find unique value count through Vlookup or by other method, vlookup, spread sheet

vlookup, spread sheet, wizard: 1. Click somewhere on the data 2. Select the Data dropdown menu 3. Select PivotTable and PivotChart Report... This will bring up the PivotTable and PivotChart Wizard 4. Follow the steps in the wizard and put the item you want counted in the rows...

Excel: How to find whether a year is odd or even?, Checking whether a Value is Odd or Even

Checking whether a Value is Odd or Even: Hi Sameera, There is a specific formula in Excel that tests whether a value is Odd or Even. If I understand your requirements correctly, the formula below will correctly handle all the possible scenarios. =IF(DATE(YEAR(F169),MONTH(B169),DAY(B169))...

Excel: finding values in a column group then copying and pasting them, j1, isna

j1, isna, d1: You can do that with formulas. In E1: =IF(ISNA(MATCH(C1&D1,K$1:L$50,0)), ,INDEX(K:K,MATCH(C1&D1,K$1:L$50,0))) In F1: =IF(ISNA(MATCH(C1&D1,K$1:L$50,0)), ,INDEX(L:L,MATCH(C1&D1,K$1:L$50,0))) in G1: =IF(ISNA(MATCH(C1&D1,K$1:L$50,0)), ,INDEX(M:M,MATCH(C1&D1,K$1:L$50,0)))...

Excel: formats in excel, free microsoft excel, currency format

free microsoft excel, currency format, excel tips tricks: What does your data look like now. You have 55 hours represented as: 55:00 If you format this as a NUMBER you ll get 2.29 which is the number of DAYS that 55 hours represents. Now, multiply that value by 24 and you ll get an ACTUAL 55 which you can...

Excel: formula to calculate gallons per minute from time, stop time, 333333333333

stop time, 333333333333, gallons per minute: bo, Assume A1: gallons B1: start time C1: stop time D1: =Stop time - Start time Time is stored as the fraction of a day. So 6 hours is stored as .25 or 1/4th of a day. 8 hours is .333333333333 or 1/3rd of a day to get minutes you need to...

Excel: formula/macro, pastes, aidan

pastes, aidan, email: As I don t know what your code does, I m going to GUESS that it does exactly what you ve said - copies and pastes - which is NOT what I d usually do in code - try something like sheets( Whichever ).range( a1 ).value=sheets( Source ).range( a2 ).value ...

Excel: function that update excel 2003 cell, cell b1, circular references

cell b1, circular references, dear bill: Jack, Generally, you can t do this. It would require Excel to remember the previous values, which you ve erased and replaced with others. Excel doesn t work this way and is not designed to work this way. It s sort of possible if you want to...

Excel: Goal Seek, excel help file, excel knowledge base

excel help file, excel knowledge base: Sachinkumar, Yes, you can. The goal-seek add-in is a very complex function, which is beyond my ability to fully describe here. There are EXCELLENT articles in the Excel help-file & knowledge base on this topic, and they will tell you...

Excel: Golf, golf question, math problem

golf question, math problem, foursomes: Sorry, I KNOW what you want, but your ilustration doesn t show HOW you got there -- trial and error is a possibility. Here s trial & error solution (which gave me grp1=20,20,2,8; grp2=6,14,18,12, grp3=4,21,16,10): In A1:A12 I put the handicaps; in E1:E12...

Excel: Golf, www google co uk, google co uk

www google co uk, google co uk, golf handicap: I m not a golfer, but I do understand the question! http://www.ozgrid.com/forum/showthread.php?t=52085 has a discussion that might help, which I got from the google search http://www.google.co.uk/search?sourceid=navclient&ie=UTF-8&rlz=1T4RNWN_enGB318GB318&q=excel+golf+handicap+foursome...

Excel: how can i give a refrence field end filter data in validation list, corsa opel, astra opel

corsa opel, astra opel, opel vectra: Ercan, I don t quite follow your question, but I think maybe I understand you. let me restate your question. You want what are called dependent Drop Down lists. If you select BRAND = VW, then you want the MODEL dropdown to only show JETTA,...

Excel: grouping data/rows by text in columns, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, word development: Just select both columns, press the TAB key to move over to column 2, and then hit the SORT button. That will group all of the like categories together. Let me know if you have any other questions or comments. Also, be sure to visit my web site and...

Excel: Help :-), e mail address, hyperlink address

e mail address, hyperlink address, target address: Hi Richard, I assume that when you say loads an outlook page you mean a new e-mail message is created in Outlook. Yes, this can be done, but it does require VBA code. You mentioned doing it via a button, and it can be done this way, but I think it would...

Excel: Help on Excel, punctuation marks, vlookup

punctuation marks, vlookup, power memory: Rex, Excel is a math and equation-based system, and doesn t have an easy way to do the fuzzy interpretations that your brain can easily do. Equations work on absolute calculations, and are not so good at playing guessing games or performing interpretations...

Excel: Help on VLOOKUP & IF with Dates, vlookup, speadsheet

vlookup, speadsheet, information date: Pictures are hard for me to follow, but I m happy to look at an example sheet - my email being aidan.heritage@virgin.net - it SOUNDS as though it s a multiple condition countif, which you can do with sumproduct =sumproduct(--(FirstRange=FirstTest),--(secondrange=secondtest),--(ThirdRange=ThirdTest))...

Excel: Hi Tom, column c, customer id

column c, customer id, ogilvy: Hello Jay, In cell d1 put in the formula =vlookup(C1,$A$1:$B$18000,2,False) then drag fill it down the column to row 500. Now if you need it on another sheet, then select the data in C1:D500 and do a copy, then go to the other sheet and do a paste...

Excel: Hide Row Macro Not Working, blank cells, question thanks

blank cells, question thanks, ogilvy: Tammy, Did you try the macro I gave you? I assume there are no merged cells in column E. Assume the worksheet is not protected. Both macros work for me. Another way would be Sub AAA2() for i = 25 to 5 step -1 With worksheets( Sens...

Excel: Highlighted Cell, coloured cells, excel function

coloured cells, excel function, dear bill: Bhavan, I can t do this for you because I cannot see the conditional formatting formula you have used to create the highlighted cells in your workbook. I don t know anything about your data table (not even the field names) so I can t even guess. ...

Excel: Highlighting Cells (Conditional formatting), free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: Yes, it s possible, but not without some programming. Here s a similar tutorial that might help you figure it out: http://599cd.com/tips/excel/extend-conditional-formatting?key=AllExperts Let me know if you have any other questions or comments. Also,...

Excel: Hyperlinks, free microsoft excel, mail message

free microsoft excel, mail message, excel tips tricks: When you link to another cell, you don t get the hyperlink. I don t know how you can get it to link to the hyperlink as well. A normal copy and paste will copy the hyperlink, but just linking to that cell won t. As far as multiple emails go, you could make...

Excel: Hyperlinks in Excel, voice of experience, question thanks

voice of experience, question thanks, macros: To set this up and therefore to run the macro you will first need to write it 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...

Excel: To hide Rows If column has certain text, sheet tab, answer right

sheet tab, answer right, clicker: In a regular module, put this macro, which you need to associate with the click of the checkbox (right-click the checkbox & use assign macro): Sub Clicker() On Error Resume Next If ActiveSheet.CheckBoxes(1).Value = 1 Then For Each x In...

Excel: hiding a column until triggered by another column value or reference, target range, correct writing

target range, correct writing, sheet tab: Rob, Did you run the macro again after you put the value in J/G. The macro has to run to do work. If you want it tied to the an event so it runs everytime you edit a cell in J5:J20, then you would have to put it in a worksheet level event. Right...

Excel: to highlight related cells, cell b2, left hand corner

cell b2, left hand corner, exact effect: Sumit, There isn t any equation or function that I know of that reports the cell address of a selected cell. Thus, the exact effect you are trying to achieve isn t possible. However, I believe there IS a macro function which can do this. But...

Excel: to highlight related cells, conditional formatting, cell a1

conditional formatting, cell a1, richard florida: Sumit One simple way to handle it would be to use the conditional formatting feature, and use the formula is in conditional formatting. This would require you to put a symbol in a cell to activate the formatting for example--- Select the cell or cells...

Excel: to highlighted related cells, follow ups, vba

follow ups, vba, aidan: I think this could be done with VBA though it might be a little slow - would the range of cells be a defined range, or would it have to work through all possible cells? And is VBA actually allowed as a method? email if it helps with follow ups is aidan.heritage@virgin.ne...

Excel: hit a wall in excel :(, vlookup, excel spreadsheet

vlookup, excel spreadsheet, roadblocks: Actually, this should be fairly straightforward - a COUNTIF function would return the number of times the part number exists, so =if(countif(YourPartNumberRange,YourPartNumber) 0,Vlookup(etc), Not present ) would do the job - replace YourPartNumberRange,...

Excel: hit a wall with excel :(, excel, isna

excel, isna, vlookup: In this case you can use the same VLOOKUP formula that you used for the formula, and check for error using the ISNA formula. Something like: =IF(ISNA(VLOOKUP(...)), No record found ,VLOOKUP(...)) This will return No record found in case that the VLOOKUP...

Excel: hit a wall in excel :(, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, vlookup: In your VLOOKUP statement, use the EXACT MATCH option: =VLOOKUP(A1,MyTable,2,FALSE) This will return an ERROR if the exact item isn t found. Then you could use the ISERROR function to display a message that the item isn t found. Let me know if you...

Excel: hit a wall in excel :(, vlookup, excel spreadsheet

vlookup, excel spreadsheet, roadblocks: You can see if something s not found via something like this: =IF(ISERROR(VLOOKUP(whatever,wherever,col,FALSE)), NOT FOUND ,VLOOKUP(whatever,wherever,col,FALSE)) If that doesn t help, then send a sample to me at bobumlas@yahoo.com, use subject of AllExpertsQ...

Excel: Import Image Macro (Excel 2007), filename searches, c images

filename searches, c images, emf files: sepp, Sub ABCD() Dim pic As Picture Dim sPath as String, sName as String Range( C4 ).Select sPath = C:Images sName = range( E56 ).Value if lcase(Right(sName,4)) .emf then sName = sName & .EMF End if if dir(sPath & sName) then...

Excel: Importing data from multiple sheet in one sheet, thanks in advance, a1

thanks in advance, a1, cells: Sub AnswerUntester() Sheet1.UsedRange.Copy Sheet4.Range( A1 ).PasteSpecial Sheet2.UsedRange.Copy Sheet4.Cells(Sheet4.UsedRange.Rows.Count 1, 1).PasteSpecial Sheet3.UsedRange.Copy Sheet4.Cells(Sheet4.UsedRange.Rows.Count 1, 1).PasteSpecial...

Excel: Index Match Function Help, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: INDEX, MATCH, VLOOKUP, and all of those related formulae only return VALUES, not formatting information. You would have to do it with a little programming. Here s a little tutorial I made a while back that isn t exactly what you want, but might be enough...

Excel: Input boxes and using input in a FOR statement, c cells, b cells

c cells, b cells, srow: You are asking the user to enter a range by using type 8, so an input such as A10 would be fine, but just typing (say) 10 will be invalid as 10 cannot be interpreted as a range - I m not exactly clear WHAT it was you wanted to do, but possibly having the input...

Excel: Interlacing 2 columns using a macro, blank cells, excel spreadsheet

blank cells, excel spreadsheet, column c: Tami, I will assume column C is available and deleting A and B will not cause problems after C is filled with the data. Sub interlaceData() Dim r as range, cell as Range, rw as Long set r = Range(cells(1, A ),cells(rows.count, A ).End(xlup)) ...

Excel: individual cell data from a master worksheet to many other worksheets, cell d5, example answer

cell d5, example answer, indirect address: I think you will need vba to achieve it, it would need to run on a change event of the worksheet - presumably firing on a change in column D. You have specified that row 5 moves across - is this always row 5, and should it go to row 5 on the destination sheet...

Excel: More information in ONE cell, coloumn, horror movies

coloumn, horror movies, horror movie: Endre, I also collect DVDs and have about 600 in my library. My solution to this problem is to define a GENRE and a SUB-GENRE. Thus I have HORROR movies, which have various sub-types VAMPIRE, DEAD, TEENAGER, etc. This approach isn t EXACTLY what...

Excel: insert rows without losing formulas, password selection, password answer

password selection, password answer, macro code: Dave, Try this (worked for me) Sub abc() Dim r As Range ActiveSheet.Unprotect ( password ) Set r = Selection.EntireRow r.Copy r.Offset(1, 0).Insert Shift:=xlDown r.Offset(1, 0).SpecialCells(xlConstants).ClearContents Application.CutCopyMode...

Excel: invoice #s, invoice number, data validation

invoice number, data validation, richard florida: Beth Here is a method that might work for you-- You could use the data validation feature (not what it was designed for) to produce a list and reference it in the invoice number cell. So when you clic on the cell the list appears. To do this-- First...

Excel: Keep array as a value in a cell, array formula, array values

array formula, array values, workday: Thats one heck of a formula - but did you check the WORKDAY function that s already built into excel before you came up with it? As far as I can tell from the formula it would PRETTY much do the job for you - the only issue seems to be that you have Saturday...

Excel: Leave Calculation. Exact Month and Days, pencil and paper, sensitive matter

pencil and paper, sensitive matter, excel sheet: Bhavan, Do you still need help with this? if you do, please start a NEW QUESTION, [not a followup] that explains exactly what you need exactly what you are trying to do. Pretend that I don t know anything about your previous question. Otherwise,...

Excel: Linking Text, different Columns, excel, filter

excel, filter: You can do that with the autofilter (menu Data- Filter- Autofilter) It is not a formula, but it will allow you to use drop downs to show all the records that fulfill both conditions. If you just need the numbers, you could use SUMPRODUCT, something like:...

Excel: Listing text in cells by highlighted color, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, excel tips tricks: Stuart, while I m sure this is certainly possible, I don t have the code for it offhand, and without taking the time to create this sheet for you, I couldn t give you much more information off the top of my head. Yes, I m sure you could use VBA code to...

Excel: Looking up data from a table, in which the data being pulled has multiple criteria or variables, wholesaler, variables

wholesaler, variables, merv rating: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. ----------- I lead free 1-hour Excel webinars on tips & tricks or on What s new in Excel 2007: check out http://www.iil.com/webinars/...

Excel: Looking up and returning data, 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: Looking up and returning data, vlookup function, database column

vlookup function, database column, database note: Kurani, All you need is to use the VLOOKUP function in column B, next to where you type SUN. VLOOKUP will return is hot from sheet 2. I ve attached my tutorial on VLOOKUP, and you should also read the function guide (Excel Help) about VLOOKUP....

Excel: Lookup lists, vlookup, rep sales

vlookup, rep sales, best bet: Sean, Ashish Mathur has documented the steps/formulas here: http://office.microsoft.com/en-us/excel/HA012260381033.aspx#Return%20multiple%20corresponding It is in much more detailed than I could capture here and is illustrated, so I think a link like...

Excel: Lookup Macro, cell c2, column c

cell c2, column c, month of april: Erin, I don t see any need for a macro here as implied in your subject. IN column C of the May sheet, put in the formula in the May sheet in cell C2 =Index(June!$B:$B,match(A2,June!$A:$A,0),1) Now drag fill it down the column. This should...

Excel: Lookup ref, max and min, excel, min

excel, min, max: For this formula I will assume that the months in column B are sequential, as I will use them to determine the range. With that, you may try a formula like: =MAX(OFFSET(E1,MATCH(1,B:B,0)-1,0,MATCH(2,B:B,0)-MATCH(1,B:B,0)))-MIN(OFFSET(E1,MATCH(1,B:B,0)-1,0,MATCH(2,B:B,0)-MATCH(1,B:B,0)))...

Excel: Lookup and sum, external criterion, tina tina

external criterion, tina tina, excel function: Tina, There are several ways to do this. You can use the DSUM() database function. It requires a special external criterion range for each instance of DSUM. So you would need one 2-cell criterion range per room SUMIFS()- does exactly...

Excel: Looping an insert row formula through a column, cell a1, blank rows

cell a1, blank rows, vba code: You don t say where your data is, so I ll assume that it s in col A, starting in cell A1, and continuous down the col for the ~7000 rows. The vba code to insert the rows as you want is: Sub insert5rows() Dim rngInsertBelow As Range Application.Calculation...

Excel: line chart does not start at 0, search option, best wishes

search option, best wishes: Are you talking here about the scale - if so, thats easy, as you just right click it and set the scale that you want including starting and ending values. If you are talking about the line itself starting in the air, then I dont think this is possible -however,...

Excel: links?, mail message, message outlook

mail message, message outlook, straight copy: mailing multiple people would best be done via mailmerge, but you could use macros - see ron debruins site (http://www.rondebruin.nl/tips.htm) and explanation. As to copying all from an existing cell, a formula wouldn t do it, the only method I can think...

Excel: looking up data dependent on one field, pivottable, report wizard

pivottable, report wizard, colum: This can be done using a PivotTable To start a PivotTable take the following steps: 1. For your raw data, make sure each column of data has a title 2. Click somewhere on the raw data 3. Select the Data drop down menu 4. Select PivotTable and PivotChart...

Excel: looking for a value in one column through different worksheets, serial number data, sheet1

serial number data, sheet1, sh1: Cesar, Assume you have 6 sheets. The first sheet (sheet1) has a place to enter a serial number in A4 and then you run the macro to retrieve all the information from that serial number. The other 5 sheets are the ones you spoke of that contain serial number...

Excel: lookup in excel, max minus min values, gas consumption, gas meter

gas consumption, gas meter, cells: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. ----------- I lead free 1-hour Excel webinars on tips & tricks or on What s new in Excel 2007: check out http://www.iil.com/webinars/...

Excel: lookup value multiple times in an array, schedule period, column head

schedule period, column head, class lists: Matthew, If you still need a solution for this, I may finally have developed the germ of an idea for you. One further rule is that no student ID can occur more than once in the pair of columns that designate a period; if it did, that would mean...

Excel: lookups, external criterion, lookup functions

external criterion, lookup functions, vlookup: Jon, This is a limitation of the VLOOKUP and MATCH functions (whichever one you used). These lookup functions stop at the first occurrence of the value they are searching for. There are three possible solutions: 1) SORT the database in ascending...

Excel: lookups!, excel, array formula

excel, array formula: The Ctrl+Shift+Enter signals Excel to evaluate the formula as an array formula. You can read a good introduction to this concept here: http://www.cpearson.com/excel/ArrayFormulas.aspx Regarding the OFFSET, this formula was intended to show the lower result...

Excel: looping indefinitely, tom hope, dear tom

tom hope, dear tom, query search: dim sAddr as String For Each Sh In Worksheets If Sh.Name = sh2 Or Sh.Name = sh3 Or Sh.Name = sh5 Then Set r = Sh.Columns(col).Find( _ What:=acct,_ After:=Sh.Cells(Rows.Count, col),_ LookIn:=xlValues, _ LookAt:=xlWhole,...

Excel: MACRO TO CHANGE THE COLOUR OF NUMBER OR BACKGROUND, conditional formatting, cell a1

conditional formatting, cell a1, format font: Select cell A1. From main Excel menu choose Format, Conditional Formatting, Condition 1:, Cell Value Is, greater than, 0, Format, Font, Color, [Bright Green], OK, Add, Condition 2:, Cell Value Is, less than, 0, Format, Font, Color, [Red], OK, OK. Now if...

Excel: Macro Code..., godown, macro code

godown, macro code, excel spreadsheet: Stephen, This seemed to work for me: (change sheet6 to the name of the sheet where you want this behavior). Sub GODOWN() Dim r As Range, r1 As Range, r2 As Range Dim rr As Range If LCase(ActiveSheet.Name) sheet6 Then If ActiveCell.Column...

Excel: Macro for Copy Pause Copy, a3 b3, loop through

a3 b3, loop through, ogilvy: Jun, based on what you describe, I would see it something like this. Excel will loop in the order you describe. Sub ABC() Dim cell as Range, newHour, newMinute, newSecond, waitTime for each cell in Range( A1:B4 ) cell.copy newHour = Hour(Now())...

Excel: Macro to color cells based on criteria, color cells, class labels

color cells, class labels, asset classes: Nitya, I used column M and N because you didn t tell me where that information was retained. It doesn t have to be there; it doesn t have to be on the same sheet; it doesn t have to be altered to just reflect what is in that particular report, and order...

Excel: Macro to create sheet with formatting., cell headers, shee

cell headers, shee, thanks for your help: There isn t really such a command - although you can copy the shee to a new book, that doesn t do the formatting for you - possibly the EASIEST way would be to save the single sheet as a workbook, then get your macro to open it, copy the sheet to the current...

Excel: Macro to create shet with formatting, cell headers, wb

cell headers, wb, thanks for your help: You can record a macro that makes a copy of the entire sheet, which will have all of the formatting that you want. Use that code to create the sheet and then just add code to that deletes any data in that new sheet that you don t want/need. I am fairly certain...

Excel: Macro: Delete Row with Certain Text, text don, msgbox

text don, msgbox, instr: Try something like this: Dim Counter As Integer Dim X As Integer On Error Resume Next For X = 6 To 1 Step -1 If InStr(1, Range( A1 ).Offset(X - 1, 0), RW ) 0 Then Range( A1 ).Offset(X - 1, 0).EntireRow.Delete Counter = Counter...

Excel: Macro: Deleting Rows with Certain Text, text don, aidan

text don, aidan, macros: much easier to do if left(YourCell,2)= RW then delete it by whatever means you are using end if where YourCell is whatever you are using to reference the cell Hope this is clear, but do let me know if not or if I can help further - my email if...

Excel: Macro data validation before copy, false selection, data validation

false selection, data validation, macro data: You have to loop thru each cell FIRST, something like: Dim Data_Complete as Boolean For X = 1 to 12 checks to see if the cell has data If Range( A5 ).Offset(X-1,0) = Then Data_Complete = False End if Next X If Data_Complete =...

Excel: Macro to find current week, dt1, e25

dt1, e25, ogilvy: Alex Normand, Lightly tested, but worked for me. Sub ABCD() Dim dt As Date, dt1 As Date Dim l As Long dt = Date l = Weekday(Date, vbMonday) dt1 = Date - l + 1 Set r = Range( EV25:IV25 ) res = Application.Match(CLng(dt1), r, 0) If Not IsError(res)...

Excel: Macro Issue, blank row, application range

blank row, application range, issue question: No files yet Nik? I m still not clear what the week numbers are going to be - are they week numbers within the current year - if so, that should be easy enough to deal with - work out the first monday (or whatever) of the current year and add x*7 to it -...

Excel: Macro modification, page layout view, page breaks

page layout view, page breaks, page break: Vish, I opened a new workbook and placed upper and lower case X s in several cells in column A. I then copied the top part of the code from your posting and modified it to select rr if it was a legitimate range. Sub WM_Archive() Dim wk As Workbook...

Excel: Macro to organize list in descending order, key field, ogilvy

key field, ogilvy, constants: Rico, it would just be a sort of that region, with column B as the key field and choosing descending as the order Sub SortData() Dim r As Range Set r = Range( A1 ).CurrentRegion.Resize(, 2) r.Sort Key1:=Range( B1 ), Order1:=xlDescending, Header:=xlNo...

Excel: Macros, conditional formatting, header row

conditional formatting, header row, sh1: Danielle the following worked for me. change the line v = Array( Sheet1 , Sheet3 , Sheet4 , Sheet5 , Sheet7 ) in the actual code to reflect your 5 sheet names. Sub copyData() Dim r As Range, r1 As Range Dim r2 As Range, r3 As Range,...

Excel: Macros and buttons, command button, design mode

command button, design mode, macros: From main menu, choose view, toolbars, control toolbox. Use the control toolbox to place a command button on the worksheet. While still in design mode, double-click on that button, and it will take you to the Visual Basic Editor, where you add the code to...

Excel: Managing macros when updating a worksheet, vba module, vba code

vba module, vba code, sort buttons: Robert code doesn t adjust like cell references in worksheets. So one way is to design your code to be dynamic. Since this sounds like a database/list, it is likely you have column headers in row 1, so in the code to sort on the header Last_Name (the...

Excel: Max IF, array formula, maximum values

array formula, maximum values, minumum: It should still work, but you need to amend my IF statement - currently I m using an impossibly small value (0) which shouldn t be returned - so instead, you need to use an impossibly large value - I don t know what range of numbers you are working with, but...

Excel: Merging cells, sh1, jrt

sh1, jrt, rtu: hong nguyen, I assume this is a VBA question and as such you would know what to do with a macro provided in response. This did what I expected from your data - whether I have understood exactly what you want (such as I didn t do anything with column...

Excel: Merging Excel Worksheets, having a bad day, logical steps

having a bad day, logical steps, filesearch: I think perhaps your tech support people were having a bad day? The process that they outline is about right - but lets state it in logical steps for every file in a given directory. Open it and for every row that is found, copy it to the destination...

Excel: Microsoft Excel Question, excel, date

excel, date: One way of providing a note could be with a formula like this on the place where you want the note to appear: =IF(TODAY()+45 A1, Warning: Payment is coming due , ) Change the reference from A1 to the cell with the due date. You can format the cell as you...

Excel: Move columns from one sheet to another, column headings, sheet1

column headings, sheet1, project id: manually, this can be done by selecting the columns as a block - just hold down ctrl, and then copying and pasting to destination - then move the columns around as necessary (or just copy them one at a time in the right order). Via a macro it would be easier...

Excel: Move file from 1 folder to another, target cell, estimate range

target cell, estimate range, scope of work: Steven, You didn t say where you got the error, but try this: 1) Dim target, cell as Range Target is a variant, cell is a range to dimension a variable, each variable must be specified individually or they default to variant. Same with I and...

Excel: MS Excel 2007 Linkage Question, dependant, linkage

dependant, linkage, ms excel: Without the dependant files being open, the data wouldn t update, so the only sure way to have the updates current would indeed be to open all files - one way round the issue would be to set the sheets to open and close all linked files just before the sheet...

Excel: MS-Excel Array Formulas - Count excluding nulls, array formula, array formulas

array formula, array formulas, null values: You didn t specify what your formula was, but I d use something like =SUMPRODUCT(--(A1:A4=0),--(C1:C4=1),--(NOT(ISBLANK(A1:A4)))) which isn t an array formula - in this case, I m counting zero values in a1:a4 with 1 s in C1:c4 and NOT being blank in...

Excel: MS Office Home & Student 2007, blank worksheet, time issues

blank worksheet, time issues, memory usage: Wes, This sounds like something wrong with your computer in general, and not a specific Excel problem. Do you have time issues with MS Word or other programs you re using? Perhaps you have limited memory in your PC? Or maybe you have many applications...

Excel: Mulit worksheet list building, index formula, relevant values

index formula, relevant values, incorrect values: Follow my original instructions exactly. As I said, you may replace each 50 in my formulas with 1000 . It will work. For instance, on the Jan sheet, enter incorrect cost code in cells L145 and L146. You will find that on the 13th sheet, the corresponding...

Excel: Multiple Auto Insert Date, cell b1, keyboard shortcut

cell b1, keyboard shortcut, cell a1: As I mentioned, the macro should set a VALUE not the formula - again, I don t know what hte original macro does, but IF I was doing this I d use the change event of the worksheet - if the column being altered was E, then F would be set to the current time...

Excel: Multiple criteria in sumif function, sumproduct, excel function

sumproduct, excel function, minus signs: Mitali, No, SUMIF can only be used with ONE criterion. However, you have a couple of options: Method 0: [Excel 2007 Only] Use SUMIFS. This is just like SUMIF except it allows multiple criteria. The Excel function reference will tell you how to...

Excel: Multiple question sets from word to excel, paragraph marks, question sets

paragraph marks, question sets, local area network: Sorry, I m not sure I can see any logic in the process (in terms of how to write a program, which would need to follow logical steps) - if the questions are all one paragraph long, then there is perhaps some logic, but if they are broken with paragraph...

Excel: Multiple Y Axis chart, axis values, xy scatter

axis values, xy scatter, scatter plot: Andy, Excel only allows two Y axes. You could consider creating fake axes using graphical (drawing) elements, but these would not automatically scale, nor have all the other nice properties that a real axis has. Another trick might be to make...

Excel: m.s excel, concatenation operation, text functions

concatenation operation, text functions, spread sheet: Suchith, There is no option for this. It is impossible to store more than one value in a cell. It cannot be done. You can play some tricks to make it APPEAR as if there is more than one value per cell. For instance, if you convert two numbers...

Excel: macro, elseif, line spacing

elseif, line spacing, numerical value: Todd, here is information copied from the immediate window: Endrow = 100 ? endrow 100 endrow = 10 ? endrow 10 endrow = cells(rows.Count,5).End(xlup).row ? Endrow 100 I set Endrow to 10 just so you wouldn t think my code didn t change...

Excel: macro in excel, proper syntax, empty cells

proper syntax, empty cells, macro in excel: Jennifer Assuming entry_date is a string variable or a variant, perhaps if entry_date = then exit sub if len(trim(entry_Date)) = 0 then exit sub if entry_Date = vbNullString then ext sub if isempty(entry_Date) then exit sub so if you...

Excel: macro for identifying the valid mail address, mail ids, valid mail address

mail ids, valid mail address, mail id: I m not sure how you would want to validate - you can certainly check for the existence of an @ sign, but that doesn t make an email valid - if you are looking to check emails that won t bounce, the only way I can think is to mail them. If you can clarify...

Excel: macro for producing pivot table, cell a1, pivottables

cell a1, pivottables, question thanks: Ralph, looks like there is a missing parenthesis ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ r.Address(1,1,xlR1C1,True).CreatePivotTable TableDestination:= , TableName:= _ PivotTable3 , DefaultVersion:=xlPivotTableVersion10...

Excel: macro to repeat, paste application, cursor

paste application, cursor: Not sure what suggestions you have been given but if the ones you have been given are the lines of you code then, politely, the code is doing more work than it needs to do. Specifically you don t need to ever select a cell in code - it s not wrong to do so,...

Excel: macro table, array formula, index function

array formula, index function, vba macro: Hi Karsten, No problem. - Add this formula to cell J1 on the print sheet: =COUNTIF(Sheet1!$A$1:$A$55,$G$1) - rightclick the print sheet s tab and paste this code: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target,...

Excel: macro table, format font, c13

format font, c13, rmb: On Sheet2, cell A13, enter Customer . In C13:H13, enter: Date, USD, HKD, RMB, BC, SV. White-font or hide this row 13 if desired. On Sheet2, Cell I3, enter: =MATCH($G$1,OFFSET(Sheet1!$A$3:$A$8,SUM(I$2:I2),0),0)+I2 and copy this formula to I3:I12. White-font...

Excel: macros in Excel, macros in excel, drag button

macros in excel, drag button, custom button: Hi Julie, In reading your question, I am assuming you have created a macro for converting the number and now want to create a button. Correct? In excel 2003 or earlier: Tools/Customize/New (creates a new toolbar if desired) Too add to an existing toolbar,...

Excel: mathematical functions using summation, i, k, and n, capital sigma, handbook of mathematics

capital sigma, handbook of mathematics, input cells: Ron, I am quite familiar with the notation you describe. Do the formula for n as n varies from i to j. Add in each new term. So I suspect that what you mean by streamline the calculation is, you want to create a formula that does this type...

Excel: merging 2 excel spreadsheets, excel spreadsheets, hotel code

excel spreadsheets, hotel code, countif: you need to combine a countif and a vlookup http://www.aidanheritage.byethost3.com/excel/xlfdic01.xls has an explanation of these and other functions it s basically something like =if(countif(Workbook2range,Hotelcode)=0, ,vlookup(hotelcode,Workbook2BigRange,2,false))...

Excel: min if, countif, b4

countif, b4, dependant: Highest B, Lowest A: ctrl/shift/enter: =MIN(A1:A4*(IF(1*(OFFSET(A1:A4,,1)=MAX(B1:B4))=0,1000000000,OFFSET(A1:A4,,1)=MAX(B1:B4)))) Highest A, Lowest B: ctrl/shift/enter: =MIN(B1:B4*(IF(1*(OFFSET(B1:B4,,-1)=MAX(A1:A4))=0,1000000000,OFFSET(B1:B4,,-1)=MAX(A1:A4)))...

Excel: multi lookup, input cells, input cell

input cells, input cell, vlookup formula: Hitesh, Here s your formula broken into the three main parts of an IF statement: IF( {beginning of the IF statement} ISNA(VLOOKUP(C9, Consumables Codes !$B$1:$F$200,5,FALSE)), {this is the...

Excel: #N/A needs to read "No", lookup reference, conditional test

lookup reference, conditional test, blank cell: Ami, Yes, you can change #NA to No using an IF statement that s based upon the ISNA() conditional test. It will look something like this: =IF(ISNA(some-value), No ,some-value) This will display No if some value is #NA, otherwise...

Excel: Narrowing extense validation lists by typing first characters, data validation, excel worksheet

data validation, excel worksheet, drop down list: I am confused. If you have a drop down arrow, as a result of having applied Data Validation, so that then the user can use the drop down list to select an item from the drop down then why do you need to use AutoComplete? The ONLY items you can enter...

Excel: Need a Formula to Search Upwards, excel, find up

excel, find up: You are right, search up is not easy, as the formulas tend to give the results that they find first on the range, and trying to define those range to use INDIRECT can get really complicated. In this case my suggestion would be to use helper columns. You...

Excel: Need help in Macros, peoplesoft consultant, aa bb

peoplesoft consultant, aa bb, blank rows: To sum cols T, V, X, Z: Sub insertAndSum() Dim insertAbove As Range, rngToSum As Range Dim rngResult As Range Set insertAbove = Range( s2 ) Do Do Set insertAbove = insertAbove.Offset(1) Loop Until insertAbove...

Excel: Page display, window tabs, hello bill

window tabs, hello bill, center button: John, Yes, you can. Each page has a triple set of indicators that maximize, close (minimize) or allow you to change the size of the page, just like the three markers X, _, and box located in the upper right corner of all Windows applications. These...

Excel: To Paste Jpg Images form Folder, employee id number, string s2

employee id number, string s2, myfolder: The Application.FileSearch has gone in office 2007 - though heaven knows why - this code With Application.FileDialog(msoFileDialogFolderPicker) .InitialFileName = Application.DefaultFilePath & .Title = Please select a folder to list...

Excel: To Paste Jpg Images form Folder, employee id number, myfolder

employee id number, myfolder, passport size: I don t know of a way for Excel to open a .jpg file -- the process would normally be done outside Excel using copy & paste into the excel sheet. You could use the macro you re using and change the hyperlink to point to the jpg (change doc = Dir(FLD & Cells(j,...

Excel: Percentages, cell c3, verison

cell c3, verison, ogilvy: Janelle, assume cell C3 holds the value 10 in cell D3 put in the formula =C3*1.5 or =C3+(C3*50%) this will display the value 15 in D3 The 1.0 gives you the original value and the .5 adds 50% or in the second verison, it multiplies the value...

Excel: Performing 2 Dimensional look-ups, pivot table, natural solution

pivot table, natural solution, huge list: Grahame, Have you tried applying a PIVOT TABLE to this problem? It seems like a natural solution. Just put the STATION in the column area and the DATE in the row area. Put the OUTPUT (the SUM OF) in the DATA area. Without writing a single equation...

Excel: PIvot Tables and VBA, r1 value, r2a

r1 value, r2a, sh2: Nardine, If you are not in a big hurry I can probably help. Also, I will probably need to get some clarification from you. I definitely need to know exactly what you need done - don t send a big file(s) and expect me to guess (big files usually are...

Excel: Pivot Chart, column headings, max values

column headings, max values, chart excel: Sara, Why did you title this Pivot chart , and then make no mention of an associated pivot table? I ll have to assume that you just have a regular data table (that you are free to manipulate) and are not dealing with pivot table data. To create...

Excel: Pivot Table Top 10 based on only one of many columns, table toolbar, pivot table

table toolbar, pivot table, customer field: Hi Ann, If I understand your question correctly, you want to change the field that you use to sort the data. To do this: -click on the customer field -click on Pivot Table dropdown on the Pivot Table toolbar -select Sort and Top 10 -select Descending...

Excel: Pivot table, creating pivot tables, pivot table

creating pivot tables, pivot table, field positions: 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: Populating a Database from Excel Worksheet, city state zipcode, input cells

city state zipcode, input cells, paste values: William, I believe you mean Bill Hermanson rather than Bob as the author of that discourse as that is more his style and Bob is quite a prolific coder, so he would have offered a macro solution. I don t believe I was contacted. Because your data is...

Excel: Pre loading a combo box...?, john walkenbach, host cells

john walkenbach, host cells, combobox: Jason, It sounds like you are linking your combobox to the range in the worksheet. You don t have to (and make sure you remove that link if you use the below). Private Sub Userform_Initialize() dim r as Range code to do the advanced filter...

Excel: Pre loading a combo box...?, combo box, filter code

combo box, filter code, combobox: I think I helped you with this yesterday. Seeing HOW you do this would be helpful. I am not sure what you mean by the preload feature. Politely, please understand and remember that I can not see what you see, know what you know about your data, know how...

Excel: Presenting information in a different format, indirect address, proper numbers

indirect address, proper numbers, excel function: Azeem, This is a perfect application for the combination of INDIRECT() and ADDRESS(). I use this all the time to rearrange cell values. Everywhere you want a cell value, you will have an equation that looks like this: =INDIRECT(ADDRESS(parameters))...

Excel: Print Preview Macro help, outdated video driver, e drawing

outdated video driver, e drawing, combo boxes: You may have an outdated video driver. Check to see if you have the latest and update. If you do have the latest driver then try unloading and reloading the userform instead of hiding and unhiding. Hiding and unhiding may be the preferred way but unloading...

Excel: Problem with a SORT, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: You ll have to separate the street number from the rest of the street name. Not very easy to do. 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: Problem with Sort/Paste Macro, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: This happens if you already have an object (formula, function, sheet, etc.) with the same name. Open up the VB editor and look for it. 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: Problems with VBA, lcase, bharat

lcase, bharat, vba: Bharat, Unless you really want a formula in column I and I can t see why you would, this worked for me. Sub Match_Rows() Dim x As Long Dim y As Long Dim s As String Dim s1 As String Dim lastrow As Long Columns( I:I ).Select Selection.NumberFormat...

Excel: Product Catalogue Spreadsheet, section headings, column headings

section headings, column headings, catalogue section: Antoni, You did a great job guessing the answer, because I think you ve gotten it right! However, there is no attachment in your email so I can t see what you ve done so far. But your intention is quite clear. All you have to do is make a small...

Excel: Protection the Excel cells, excel, autofilter

excel, autofilter, protection: When you protect the sheet, there is one check box for enabling the autofilter on the section Allow all users of this worksheet to: . If you don t see it on the initial list, scroll down on that window, as it is the fourth option from the bottom. Hope...

Excel: Pulling out data into multiple worksheets, food sheet, microsoft site

food sheet, microsoft site, column c: Jessica, Ashish Mathur has documented how to do this at the microsoft site. It is a much richer explanation than I could give in this environment, so I will post the link to it. http://office.microsoft.com/en-us/excel/HA012260381033.aspx#Return%20multiple%20corresponding...

Excel: Push and Pop, input worksheet, index formula

input worksheet, index formula, circular reference: Mike, In order for a cell to receive a value, there MUST be an equation in that cell, ready to resolve to (or equate to) that value. The only way to put a value into a cell that does NOT require an equation in the cell, is to use a macro. I don...

Excel: is it possible to grab data depending on a colums value?, countif, pivot table

countif, pivot table, colum: A pivot table would be the easiest way to go so that you can analyze the data just by dropping it into the appropriate area - but a COUNTIF or SUMIF function could also be used - these count values that fit criteria - so =countif(a1:a100, M ) would...

Excel: problem running excel 2002 macro in excel 2007, macro in excel, file in c

macro in excel, file in c, c drive: OK, this is an operating system issue really, as Vista has issues regarding permissions - the easiest fix would be to write to a public folder - so probably not one on C drive. Otherwise, I m not sure how to override it - the macro would need to run as an...

Excel: Query regarding Creating a data base in Excel., total value, data base

total value, data base: Sorry, I don t understand. What do you mean by an amount calculated against them ? How is this different from an amount ? What do you mean by add up amounts assigned to them ? How is THIS different from an amount ? How it the total value from sheet2...

Excel: Question on Date and Time, target value, circular references

target value, circular references, sheet tab: Padmini, that is the way formulas work - so unless you want to use intentional circular references (and unless you are well schooled in them I wouldn t go that route), then you will need a macro. right click on the sheet tab where you want this behavior...

Excel: qu, good answer, correct answer

good answer, correct answer, google: Steve Sorry I couldn t get back to you quicker I had a bad router. I cannot find anywhere that there is the ability to set up the hidden column toggle feature in excel. I don t doubt that it can be done, but I, like you, can t find it anywhere. I suggest...

Excel: qu, www google co uk, google search

www google co uk, google search, hidden columns: I m not aware of the dialogue box you are referring to, but it may just be the definition that is throwing me - having said that, there IS a dialog box in Access that looks as you describe, so I m wondering if that was what you had in mind. My google search...

Excel: qu, correct answer, google

correct answer, google, spreadsheet: Hi Steve How are you? I am 99% sure that such a feature is not built in Excel 2003. You should have seen a user defined macro that displays a form with the info about each hidden column and a check box to hide or unhide it. How familiar are you with...

Excel: question re: dates, second date, text strings

second date, text strings, ogilvy: Hardeeo, Assume the dates are in A1, A2, and A3 A1: Date to use in comparison A2: First date A3: Second date in B2 put in =if(A1 A2, yes , no ) in B3 put in =if(A1 A3, yes , no ) If you wanted it to be less than both 1 and 2 to...

Excel: Radio Buttons, incorrect responses, cell a1

incorrect responses, cell a1, radio buttons: Example: In cell A1, you have a number of products. In B1, you have a dollar amount spent. In C1, you want to display A if A1 is 4 AND B1 is $500. You want it to display B if A1 5 and B1 is $500. Otherwise, you want it to display C . In C1,...

Excel: Range problem for graph, element changes, second element

element changes, second element, tricky question: You need to use the INDIRECT worksheet function =indirect(EVModel!$E$12:CONCATENATE( EVModel! ,ADDRESS(13,Dashboard 2!R2))) which converts text that looks like a reference into the reference itself. should it be useful here or at any point, my email...

Excel: Reference cells, excel, format

excel, format: Check the format of the destination cell (menu Format- Cell) and be sure that it is set to General. This way it should reflect whatever format is on the origin cell. If that doesn t work, try inserting a new worksheet on the book and try the formula there....

Excel: Referencing and displaying problem, validation logic, data validation

validation logic, data validation, page workbook: Carlos, The list for a data validation drop-down MUST be in a single column or row. I made several attempts to trick Excel into thinking that four columns were one, but wasn t able to fool the Data Validation logic into accepting four columns as one....

Excel: Reg: Help with Macros - VBA, method cells, cell c1

method cells, cell c1, cell b1: if range( a1 ).value= Band 4 and range( B1 ).value= PSCS then range( C1 ).value= E,E,A,C,DB,P&C will do this for you you can reference ranges like this, you can use the OFFSET function, or you could use the CELL method cells(1,2).value=1 would...

Excel: Relative cell addresses, cell addresses, relative cell

cell addresses, relative cell, typical c: Steve Activesheet.cells(x,y).formula = =+ & cells(a,b).address(1,0) & + & cells(c,d).address(0,1) Here is a demo from the immediate window: a = 5 b = 3 c = 8 d = 5 ? =+ & cells(a,b).address(1,0) & + & cells(c,d).address(0,1) =+C$5+$E8...

Excel: Remove/Mark Duplicates in a column, ribbon group, excel sheet

ribbon group, excel sheet, thanks in advance: Julia, If you have Excel 2007 there is a built-in duplicate detector in the DATA ribbon group. Its works great. If you have Excel 2003, you can COUNTIF the entire column for every occurrence of each entry in the column. COUNTIF s which yield...

Excel: Renumbering Sheets, workbook sheets, inputbox

workbook sheets, inputbox, maps: Dave, I don t know what you tested, but what you described you want is exactly what I provided a solution for with my second code snippet. After your post, just to be sure I put in the additional code to make it a complete procedure and tested it...

Excel: Renumbering Sheets, inputbox, sheet numbers

inputbox, sheet numbers: ResultFileSheets = ActiveWorkbook.Sheets.Count x = 0 For i = 1 To ResultFileSheets If i 1 And i 10 Or i = 11 Or i = 13 Then x = x + 1 SheetList = SheetList & x & - & ActiveWorkbook.Sheets(i).Name & & vbCr End If Next i...

Excel: Reply, r2 value, sh4

r2 value, sh4, follow ups: Manjari, Here is the revised macro: Sub Consolidatewithoutdups() Dim sh As Worksheet, sh4 As Worksheet Dim r As Range, r1 As Range, r2 As Range Dim i As Long, k As Long If Worksheets.Count 5 Then MsgBox Should be 5 sheets Exit Sub End If...

Excel: Count number of days, chip pearson, format date

chip pearson, format date, input values: Dennis, based on your example, I would put in =countA(A2:A19) or =count(B2:B19). the first would react to the date and the second would react to the amount in column B (assumed). In this case, the actual count is of rows that contain data....

Excel: Returning only numbers, maximum deviation, exit function

maximum deviation, exit function, rng: Function onlyNumeric(str As String) As String Dim i As Integer Dim oneChr As String onlyNumeric = If Len(str) = 0 Then Exit Function For i = 1 To Len(str) oneChr = Mid(str, i, 1) If oneChr Then ...

Excel: Room Booking worksheet problem, room reservation system, guest checks

room reservation system, guest checks, data validation: James Without knowing how your worksheet is set up it is a bit difficult for me to be specific. Perhaps you can use increments on the last day of a booked stay other than a full day, I.E. hours. I don t know what is preventing you from booking a new guest...

Excel: Rounding, cell c5, formulas in excel

cell c5, formulas in excel, richard florida: Zan I m not exactly sure about your question, because you are asking to round to the nearest 25, but in the two examples you give one is rounded up 5 (2870 to 2875) and the other is rounded down 5 (1105 to 1100) The round formulas in excel will round...

Excel: Rounding off a calculated percentage, n9, hard time

n9, hard time, b1: =+M9*0.3ROUND(N9,2) is not a valid formula regardless of the circ reference you are getting. You would need something like: =+M9*0.3 + ROUND(N9,2) A circ reference means the formula is referencing the cell where the formula is itself. Like putting...

Excel: Run a macro to anther sheet or workbook, programmer guy, false selection

programmer guy, false selection, reply thanks: You want the same macro which is currently run from a button on sheet1 to run on sheet 2 or on another workbook? How is the macro to know whether you want sheet1, sheet2, sheetx or another workbook? And, here s the SAME macro with extraneous code removed:...

Excel: Running macros on a protected worksheet, filter changes, ogilvy

filter changes, ogilvy, message box: Alan, when you protect the sheet (assuming you have xl2002 or later version), there is an option to allow autofiltering on the sheet (check the box next to use autofilter ). You must allow the user to use autofilter when you protect the sheet. To...

Excel: ranking highest occurance of largest values, occurance, occurrences

occurance, occurrences, 3k: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. ----------- I lead free 1-hour Excel webinars on tips & tricks or on What s new in Excel 2007: check out http://www.iil.com/webinars/...

Excel: reg VBA code, vba code, excel sheet

vba code, excel sheet, input box: Not sure how much help you need - Alt F11 will get you into the VBA editor, here you can design the form. The code needs to be userform1.show (or whatever your userform is called) email me if you need further help - my email being aidan.heritage@virgin.ne...

Excel: To remove / hide Screentips of Hyperlinks, myfolder, doc doc

myfolder, doc doc, filepath: Untested: Sub Hyperlink_Fast() It is compatable wit Excel 2007 Dim MyFolder As String MyFolder = \Opradnn291Calendarhandouts On Error Resume Next For j = 2 To Range( B60000 ).End(xlUp).Row Err.Clear FLD = MyFolder & ...

Excel: russell2546@hotmail.co.uk, result column, hotmail

result column, hotmail, greater than the sum: Russell, This worked for me. Put the formula in C2 and drag fill down the column. =IF(ROW()=2,IF(B2 0, ,B2),IF(B2 0,B2+IF(ISNUMBER(C1),C1,0),IF(ISNUMBER(C1),IF(B2 ABS(C1), ,C1+B2), ))) Assumes the numbers in column B will be positive or negative...

Excel: Sampling, www google co uk, random sample

www google co uk, random sample, utf 8: I m afraid that statistics isn t really an area I m strong in - so I m going to give you a google link which hopefully WILL help http://www.google.co.uk/search?sourceid=navclient&aq=3&oq=excel+random+sample&ie=UTF-8&rlz=1T4RNWN_enGB318GB318&q=excel+random+sample+selectio...

Excel: Search then copy based on matching results, student information system, string s2

student information system, string s2, conversion project: Cassandra Bilyeu, this is written for the sheets being in the same workbook. Change the sheet names to match your situation. Sub GetData() Dim s1 As String, s2 As String, s3 As String Dim sh2 As Worksheet Dim sh1 As Worksheet Dim rr As Range,...

Excel: Search for data throught worksheets and pasted in a cell of other worksheet., question thanks, few differences

question thanks, few differences, ogilvy: Cesar, You would need to generalize your function a little: Private Sub Worksheet_Change(ByVal Target As Range) Dim wtg As Variant, wf As Variant if Target.Address = $D$5 then on Error goto ErrHandler Application.enableEvents = False Call...

Excel: Search for a value across workbooks and returning adjacent cell value, cell c5, sheet names

cell c5, sheet names, sheet1: Mike, So you want to search sheet2 to sheet8 for the value in C3 of sheet1, looking in cell E5 of each sheet. Then, if a match is found, return the value in C5 of the matching sheet. I found this formula to work: =CHOOSE(MATCH(TRUE,CHOOSE({1,2,3,4,5,6,7},Sheet2!E5=C3,Sheet3!E5=C3,Sheet4!E5=C3,Sheet5!E5=C3,Sheet6!E5=C3,Sheet7!E5=C3,Sheet8!E5=C3),0),Sheet2!C5,Sheet3!C5,Sheet4!C5,Sheet5!C5,Sheet6!C5,Sheet7!C5,Sheet8!C5)...

Excel: Searching One Value in Multiple Sheet, sheet1, predefined names

sheet1, predefined names, column c: Sub ABC() Dim sh1 As Worksheet, Range1 As Range Set sh1 = Workbooks( SFMacroEnabledSheet.xls ).Worksheets( Sheet1 ) You said the source is column K:K but your code uses L:L. I will use K:K Set Range1 = Sheet1.Range( L:L ) Column as Source Set...

Excel: Second question: formula related, column c, ogilvy

column c, ogilvy, completeness: Hunter, this worked for me: =SUMIFS(D:D,C:C, ,A:A, A1) just to add and for completeness the isblank is the problem [and false isn t a function: FALSE, not FALSE()]. I believe you can only have range references in the range reference arguments....

Excel: Section Breaks, pagebreak, level sections

pagebreak, level sections, grouping data: CARLA, Section Breaks could mean a lot of different things. One thing I could think of that is supported by excel is if you had data in a list/table like a database where many of the columns have identifying information such as a column for company...

Excel: Select all cells having specific formula only., f4 key, menu function

f4 key, menu function, b18: Nabam, There are several ways to select formulas, but I am not sure if any of them will work for you. One way to do this involves FIND (not the text function, but the find & replace menu function). The formulas you seek must have something...

Excel: Selecting data, empty cells, code snippets

empty cells, code snippets, arrow keys: Katie, Why don t you copy just the filled cells to another location and use that as the source for your graph. You could copy to another sheet even. In any event, here are some code snippets that might help: If I wanted the cells with numbers in column...

Excel: Selecting data, blank cells, row numbers

blank cells, row numbers, aidan: As it s going to be for a graph, I think I d use an array to pick up the data in a seperate graphing area =LARGE(IF(ISBLANK(E2:E16),0,ROW(E2:E16)),ROW()-1) where E2:E16 represents the range you are checking for data. Row()-1 assumes this formula is...

Excel: Shift, excel function, dcount

excel function, dcount, sumproduct: Anil, You can use the COUNTIFS() function (if you have Excel 2007) OR, the advanced form of SUMPRODUCT() for 2003 or 2007, OR DCOUNT for 2003 or 2007. Take your choice! The Excel function reference will help you with COUNTIFS(). Just use TWO...

Excel: ShortCut to insert date, seperator, language options

seperator, language options, language selection: Hi Raj, To change the date format, follow these steps: -Click on the Start button and select Settings, then Control Panel -In the Control Panel, select Regional and Language Options -On the first tab (Regional Options) click on Customize next to your...

Excel: Sort grouped rows of data in excel, g9, g1

g9, g1, g7: SHELLEY in h1: =max(G1:G3) h2: =max(G1:G3) h3: =max(G1:G3) Now select H1:h3 and do copy. Then select the rest of column H down to the bottom of you data and do Edit= Paste This give me: X a1 0 0 1 1 1 =MAX(G1:G3) Y a1 1 2 1 0 22 =MAX(G1:G3)...

Excel: Sort/Paste Macro, bk2, sh2

bk2, sh2, sh1: Brett I am going to take your picture literally. I will assume you want to copy C2 of the top worksheet to A2 of the target, E2 of the top to B2 of the Target, G2 of the top to C2 of the target. I also that the source and target worksheets are the first...

Excel: Sorting, column c, gap

column c, gap, cells: That s how the sort works inherently. Are you selecting one cell or all the cells you want to include in the sort? If one cell, which may be right, there must be no gap like column C is empty while the data is in A:F, for example. If there s no gap, all should...

Excel: Sorting Data into Groups (If, then), placement test results, target level

placement test results, target level, target levels: For my answer, I put the Target Level numbers in column D starting in row one. In column E put in the following: =IF(D1 =4,1,IF(D1 =6,2,IF(D1 =8,3, N/A ))) This will sort into the groups. For the formula above, if it is over 8, the answer will...

Excel: Sorting data into, littlewing, thanks in advance

littlewing, thanks in advance, increments: Mike I can give you an idea using a nested if statement but would have a bettter idea if I could see the sheet, is that possible and if so you can email it to richard.littlewing(at)gmail(dot)com If not then some idea on how the table is set up. Richar...

Excel: Spin Button in Userform Help, data cells, userform initialize

data cells, userform initialize, bb8: In a general module, I put in code to load the userform, initialize things and then show the userform. Sub Main() Dim lastrow As Long lastrow = Worksheets( Project Data ).Cells(Rows.Count, A ).End(xlUp).Row Load UserForm1 UserForm1.SpinButton1.Max...

Excel: Split sums, vba macro, complex computer

vba macro, complex computer, perfect candidate: Heidi, Unfortunately there is no easy way to do this with formulas. It s a complex computer sciences problem requiring an ALGORITHM, therefore this problem is a perfect candidate for a MACRO. I don t offer macro advice here, so you ll have to ask a...

Excel: Splitting text in 1 cell into 2 cells, mac os 10, names thanks

mac os 10, names thanks, string functions: What I don t know is how the data is formatted - what seperates the name from the address? If for example it was the first instance of a comma, then it could be done with string functions (search, left, right) - if you can update me on this I can try and...

Excel: Spreadsheets, column c, b20

column c, b20, a21: Rob, assume on the summary sheet, the players name is in A2 If you just want the sum across a fixed number of worksheets (assume the each worksheet/week has data for only one score per player), you can use =sumif(wk1!A2:A20,A2,wk1!B2:B20)+sumif(wk2!A2:A20,A2,wk2!B2:B20)+sumif(wk3!A2:A20,A2,wk3!B2:B20)...

Excel: If/Then Statement Help, free microsoft excel, cell a1

free microsoft excel, cell a1, excel tips tricks: Depending on how many conditions you re talking about (there is a limit) then this would just be a complex IF statement: =IF(AND(A1= Team 1 ,B1 Team 1 ),3,IF(AND(A1= Team 1 ,B1= Team 1 ,6,IF(AND(A1= Team 1 ,B1= Team 1 ,C1= Team 1 ,9,0))) You can use...

Excel: IF SUM VLookup Match, leftmost column, allexperts

leftmost column, allexperts, d8: Edgar Because the SUMIFAND() doesn t really work well, one of the approaches is to create a single criteria that is made up of the multiple criteria that you are looking for. For example-- Assuming the first row in your example is row 2 and the first...

Excel: SUMPRODUCT, multiplication sign, array formula

multiplication sign, array formula, minus signs: Sarah, I was unable to make SUMPRODUCT operate with discontiguous ranges. I tried several techniques and none of them worked! So, you ll have to make a contiguous range, or try COUNTIFS(), though I don t know if COUNTIFS will allow discontiguous ranges...

Excel: More on SUMPRODUCT and --, sumproduct, false values

sumproduct, false values, excel function: Sarah, 1) yes, I meant IFS. Excel 2007 not only has COUNTIFS, but also SUMIFS and AVERAGIFS, all of which are multi-criterion record selectors which perform the COUNT, SUM, or AVERAGE functions on the records they select. 2) The -- vs the * is just...

Excel: Sum, phone num, vb script

phone num, vb script, ogilvy: Al, If you are asking me to write a macro to consolidate the data or produce the summary table, then send the workbook to twogilvy@msn.com Include an extra worksheet with an example of what you want the results to look like - you can put in dummy...

Excel: SumIF Conditional, countif, correct result

countif, correct result, h3: Andy, If you are satisfied with your SUMIF (it is giving you the correct result), you can create an AVERAGEIF by dividing SUMIF by COUNTIF. However, in Excel 2007, there already IS an AVERAGEIF statement. Why not just use that instead? ...

Excel: Summing a set of numbers generated from a function utilizing a "TODAY" to get the answer, column c, service period

column c, service period, set of numbers: As you already have the service period, I d do a multiple condition countif - which you can do with sumproduct =sumproduct(--(c1:c1000 =2),--(c1:C1000 5)) would show you the number of entries that fall between 2 and 5 years. Same principle can be used...

Excel: Sumproduct function, worksheet cells, concrete value

worksheet cells, concrete value, b2000: Mina, I ve finally thought of a possible method but can t be sure it d work without trying it.... and I haven t the time to construct an entire spreadsheet to test it. Its not entirely clear if you are looking for the text in the table of 31 entries...

Excel: Sumproduct (multi), rvp, column c

rvp, column c, expense report: The formula is right, but I d need to see the data -- perhaps RVP is really RVP (with ending space) int the data. And is it the TEXT May or a date FORMATTED as mmm so it SHOWS as May? The formula would calculate more quickly this way, by the way: =SUMPRODUCT(--(E10:E65433=...

Excel: save file as, excel application, time board

excel application, time board, text path: Hello again Mahesh, You can t incorporate FileCopy into the strTemp string for execution by extracta (I m assuming that you are passing the strTemp string to the Shell function to run extracta from VBA). You should be able to run extracta as you were before...

Excel: On-screen File Loading Animations, loading animation, windows task manager

loading animation, windows task manager, memory wall: An out of memory message would indicate to me that either some recursion is happening, or objects are being loaded and not dismissed. The quantity of sub-routines shouldn t matter (although it seems a bit overboard when they seem to be doing the same thing)...

Excel: selecting unique rows, sh1, vba macro

sh1, vba macro, ogilvy: Stuart, the easiest is to copy the sheet and delete the duplicates Sub ABC() Dim i As Long, sh As Worksheet Dim sh1 As Worksheet, lastrow As Long Dim r As Range Set sh = ActiveSheet sh.Copy after:=Worksheets(Worksheets.Count) Set sh1 = Worksheets(Worksheets.Count)...

Excel: set spellllang for different languages in same cell, dicitonary, cell content

dicitonary, cell content, different languages: No, sorry. I don t use 2007 yet and I don t see why it is not resetting the language either - looks like it should be doing so but I have never played with that feature to know for sure. Wonder if the file needs to be saved or EXCEL needs to be closed and...

Excel: specifying separated cell ranges, prediction group, linest

prediction group, linest, generic function: Tom, LINEST is HARDLY a generic function. LINEST is one of the complex functions from the forecasting & prediction group. It s too bad you didn t specify the function you were using, instead, distinctly saying you were interested in a GENERIC function...

Excel: split a string, unwanted text, cod collection

unwanted text, cod collection, agnst: Arindam One way to get you desired result is --if the numbers are consistently in the same place in the string, use text to columns, fixed width and split the string at that point. If there is a common delimiter before the number use text to columns and...

Excel: If statement, cell b5, cell b2

cell b5, cell b2, search formula: Debbie You can use the search feature to identify whether the cell contains 1057. the search will tell you what position the number starts, so if it tell you a position of 9, then you know it is there and starts at the 9th position in the text string....

Excel: If, then statements, raw score, raw scores

raw score, raw scores, human error: Im not sure Ive got enough to go on here - there is a percentile worksheet function, but with only two values to go on here Im not sure how you want to apply it. Im happy to look at sample files - my email is aidan.heritage@virgin.net I would certainly...

Excel: summarizing data from several worksheets into one, simplicity sake, indirect address

simplicity sake, indirect address, sheet names: Pat, There is an easy way to do this, I use it all the time to draw info from various sheets and cells. The beauty of it is that you don t need to create each formula individually... you can create ONE and then copy it, and all will be correct. ...

Excel: Table with two columns and the formula., array formula, a10

array formula, a10, b10: Assume there are letters in range A1:A10, and colors in B1:B10. To count the # of rows that have x in col A and red in col B, use the formula: =SUM(IF(A1:A10= x ,IF(B1:B10= red ,1,0))) After entering the formula in a cell, instead of hitting Enter,...

Excel: Text to column by vba, mail id, question pool

mail id, question pool, correct result: Hi Vimal, I saw your question in the question pool...not sure if you got your answer already or not. But I thought maybe I could help. One thought I had was to check your cell format. If your date format you have chosen has an asterik (*) next to it,...

Excel: TextBox due date, sheet1, cell a1

sheet1, cell a1, question thanks: Private Sub Workbook_Open() Load UserForm1 On Error Resume Next For i = 1 To Sheet1.Range( A50000 ).End(xlUp).Row If Sheet1.Cells(i, 1).Value Date + 4 And Sheet1.Cells(i, 1).Value = Date Then UserForm1.Listbox1.Additem Sheet1.Cells(i,...

Excel: Time difference, sheet tab, mm ss

sheet tab, mm ss, column c: Right-click the sheet tab, select View code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Column Case 1 Target.Offset(0, 1).Value = Now Case 3 Target.Offset(0, 1).Value...

Excel: Time sheet Formulas, time hh, negative result

time hh, negative result, help area: Elham The formula to calculate the actual and needed hours is simply subtracting one form the other, and formatting the start finish and needed input properly so that the calculation can be made I can t seem to get the formula to produce the negative...

Excel: TimeStamp, target cells, array formula

target cells, array formula, vbyesno: Hi Liz, Suppose you want to add a timestamp to column B whenever the user enters something in column A. - Rightclick the sheet tab and select view code - paste this code in the module window: Option Explicit Dim mbDisable As Boolean Private...

Excel: Timesheet, decimal fraction, aidan

decimal fraction, aidan, heritage: I m not sure I follow what you need to record - time is simply (to excel) a decimal fraction, with 1 hour being 1/24 - so 45 minutes is the fraction 3/96 - so just deduct this value from any times. If you can email me with a sample of your workings for...

Excel: Transfering a table from excel to word, excel sheet, different places

excel sheet, different places, aidan: OUCH! Excel is good at sums, but not good (especially) at fixed layouts - but I gues it s PROBABLY a question of pasting, then adjusting the margins and font size until it does actually fit - which is slightly trial and error, but should work - sorry to not...

Excel: Transposing data with VBA (or other methods), statistical analysis software, setting 1

statistical analysis software, setting 1, image example: Sam, Send me a sample file to twogilvy@msn.com and I will write the code. I understand there will be a dynamic number of settings extending to the right (there will be no data beyond the last setting s data). -- Regards, Tom Ogilvy...

Excel: time, days of the week, timesheet

days of the week, timesheet, heading: Is 7:30-3:30 all in one cell? No AM or PM? If not, can I assume the first is ALWAYS AM & the 2nd ALWAYS PM? Any time deducted for lunch? Maybe send a sample wb to me at bobumlas@yahoo.com and use subject of AllExpertsQ and repeat what you want to do...

Excel: To transpose data in excel, abc 123, dgj

abc 123, dgj, column data: Sub ABC456() Dim Stp As Boolean output in column C n = 0 For i = 1 To Range( A1 ).End(xlDown).Row If Cells(i, 1).Value = abc Then Stp = False n = n + 1 m = 4 Cells(n, 3).Value...

Excel: truncate text / numerics, cell a1, easy excel

cell a1, easy excel, excel function: David You can use the formula =left() Assume the cell A1 contains d4j3256 The formula Left(A1,3) will result in d4j The three in the formula tells excel how many letters or integers from the left to use in the result. Hope this helps Richard...

Excel: Unhide Worksheet, aidan, heritage

aidan, heritage, virgin: YOu haven t specified what you have tried - seeing the worksheet might help, but I d PROBABLY do it with a macro to unhide the sheets. I m suspicious that the file might be a shared workbook - if so, that could be the cause so unshare first. My email...

Excel: Unique filtering in UserForm Combo box, combo box, advanced auto

combo box, advanced auto, filter option: The last part of the the last paragraph did not make sense. You can use a loop to loop thru the entire range of where the jobs are listed and create a collection of the items. Collections do not allow for duplicates and therefore you will get your unique...

Excel: To Update Master Record with Yearly Record, column headings, looper

column headings, looper, necessary data: The line as it stands is fine, but I ve spotted that I mistyped the line above For looper = Sheets( Record 2009 ).Range( AA1 ).Value should be For looper = Sheets( Record 2009 ).Range( AA1 ).Value + 1 IF it still causes problems, can you email...

Excel: User Form - Finding the value in an Array, rrow, srow

rrow, srow, rescol: Italo, Sorry. When I was testing, I didn t want to overwrite my test data in the cell, so I commented out the line you wanted and put in a line that put the value in the textbox. That made it easier to verify I was finding the correct cell. I forgot...

Excel: UserForm Password, password question, control toolbox

password question, control toolbox, editor tools: Open the Visual Basic Editor (Tools, Macro, Visual Basic Editor). Then enter the code on the worksheet object where the command button resides. The Command Button should be placed on the worksheet using View, Toolbars, Control Toolbox. You shouldn t attempt...

Excel: Using 2 criterias!, code column, sumproduct

code column, sumproduct, pivot table: Two ways of doing this - one would be via a pivot table which would enable you to automatically get the summary you want for ALL combinations -the other is the use of SumProduct to give you a multiple condition count or sumif function =sumproduct(--(FirstRAnge=firsttest),--(SecondRange=secondtest))...

Excel: Using, linkit, ogilvy

linkit, ogilvy, mail: Mohd. That isn t what you asked for. If that is what you want: Sub buildformula() Dim r As Range, cell As Range Dim linkIt As String, Linkform As String Set r = Range( A3 , Range( A3 ).End(xlDown)) r.clear For Each cell In r linkIt = cell.Value...

Excel: universial macros, xls workbook, microsoft site

xls workbook, microsoft site, addin: Lucas, Most people put these type of macros either in there personal.xls workbook or they create an addin and have the addin manager load the addin when excel is opened (tools= Addins in xl2003 and earlier). JK Pieterise has written an extensive article...

Excel: To update Master Record with Yearly Record, yada yada yada, followup question

yada yada yada, followup question, number 0: put the entire path in this line of code: If Err.Number 0 then Workbooks.Open C:yada yadayada yadaMaster Record.xls Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) On Error Resume Next Dim WB as workbook Err.Clear...

Excel: To update Master Record with Yearly Record, column headings, current sheet

column headings, current sheet, group mode: The usedrange of sheet2 is ONLY the new records, and does not include all the cells. If you ve entered records into rows 65:70, then sheet2 s usedrange is only rows 65:70, not 1:70. I can only imagine that you have a different sheet2 being copied. Try saving...

Excel: updating cell in excel 2003, target address, visual basic for applications

target address, visual basic for applications, cell a1: It sounds like what you re asking is this. Each time you change the value of A1, you want the value in B1 to increase by the value you just entered in A1. Automation in Excel (i.e., processes that are triggered by a particular action) is effected through...

Excel: updating expenditure figures every month, aidan, 3 september

aidan, 3 september, copy paste: You will basically need to use a macro to do what you want - it s going to need to set the cumulative cells to be the actual value rather than a formula - I can write such a macro for you - it would help to see the real sheet though - my email is aidan.heritage@virgin.net...

Excel: updating figures in commulative & monthly columns., progress report, arrows

progress report, arrows, expenditures: Nabam, You CAN T delete figures that calculations depend upon to produce their answers! As you see, if you do delete a figure, the calculation answer will change. This is the fundamental way Excel works! If you want to preserve a calculated...

Excel: upgrading Excel4 macro, autoshape, pointers

autoshape, pointers, circles: You need something like: IF Sheets( sheet1b ).Range( A1 ) = Sheets( sheet2b ).Range( A1 ) Then Sheets( sheet1b ).Shapes( circle ).Copy Sheets( sheet2b ).Range( B1 ) End If This will depend on what kind of circle you have and obviously where you want...

Excel: using colour to manipulate lists of data ..., moutains, ogilvy

moutains, ogilvy, short answer: Sandy, I can t do much better than I did since you haven t told me anything. I don t know if you want the macro to copy rows of data that meet the criteria or if you want to delete rows of data that don t meet the criteria. I will assume you copy...

Excel: using ss as a tickler file, conditional formatting, format button

conditional formatting, format button, tickler file: Hi Sarah, This is a two parter. First, you need a cell on your spreadsheet that automatically updates to the current day when you open the file. Use the following formula =TODAY() Second, you need to use conditional formatting on your due date...

Excel: using vlookup, open cells, empty cells

open cells, empty cells, sheet1: William Perhaps I didn t explain my answer properly, but the method I gave you does exactly what you want to do based on my reading of the question, and the additional information in your follow-up. I suggest you try it out. The result will be that...

Excel: #VALUE, catalogue data, vlookup

catalogue data, vlookup, typing error: with the exception of missing commas in the iserror part of the formula, which I take it is a typing error, I cannot see anything obviously wrong with the formula - you have tested for an error and handled it accordingly- SO the problem must relate (I think)...

Excel: VB Coding for AutoCapitalize, proper target, target value

proper target, target value, value application: Private Sub Worksheet_Change(ByVal Target As Range) Select Case Target.Column Case 3,4,6,12,13 etc -- you get the idea Application.EnableEvents = False Target.Resize(1, 1).Value = Application.Proper(Target.Value) Application.EnableEvents = True End...

Excel: VBA code, csv files, jhmcs

csv files, jhmcs, xls file: Rouble, I assume the code you posted works and gives you a list of the files you want to save as CSV. Given that, I have added code to loop through that list, open the files and save as CSV to the specified directory. You statement about the folder being...

Excel: VBA - creating new rows, sheet1, vba code

sheet1, vba code, aidan: I d use a COUNTIF function to determine if the entry exists, and cells.SpecialCells(xlLastCell).row to pick up the last used row - which you can then use with a range or cells command - I m being a little general in this reply, but am happy to expand...

Excel: VBA events in Excel, input worksheet, cell values

input worksheet, cell values, select statement: Susan, To the best of my knowledge, VBA does not select a worksheet internally as a matter of course. there are some commands that select a sheet. I believe paste or paste special do. If you don t have a select statement in your code, then you might...

Excel: VBA forms, value rs, getrows

value rs, getrows, dim rs: If the recordset contains three fields named Field1, Field2, Field3: Display first record on textboxes rs.MoveFirst TextBox1.Value=rs.Fields( Field1 ) TextBox2.Value=rs.Fields( Field2 ) TextBox3.Value=rs.Fields( Field3 ) Display next record: ...

Excel: VBA - Inputting a value from a formula, false application, question thanks

false application, question thanks, rc 4: Ryan, the unqualified Cells(a, 1) refers to the activesheet (and thus your problem). You could either use ws.Cells(a, 1) and do this for all cell references or the easy fix is to activate the sheet you are working on Sub GasCalc() Dim...

Excel: VBA lines delete, head start, checks

head start, checks: Sub aidan20() Set currentCell = Worksheets( Sheet1 ).Range( A1 ) this will handle column A - I m assuming you want to check a specific column here it also does sheet 1- amend both these details Do While Not IsEmpty(currentCell) Set nextCell...

Excel: VBA-- modifying a graph curve to provide inputs to excel spreadsheet, tony rogers, integer values

tony rogers, integer values, paypal: Hi Tony, I m no expert on charting, but what you describe is possible (by default) in Excel 2003 and older. You sinply drag the chart point(s) up and the data is adjusted accordingly. If the chart data is a formula, Excel even suggests to use goal seek...

Excel: VBA--select and hide cells, numeric entries, accum

numeric entries, accum, specific place: Hunter If (Application.CountIf(Columns(i), *Accum* ) 0 Or _ Application.CountIf(Columns(i), *Book* ) 0) And _ application.Countif(cells(1,i).Resize(7,1), 6/30/2009 ) 0 Then columns(i).EntireColumn.Hidden = True should only look at...

Excel: VBA Transposing a specific format, array formula, readable format

array formula, readable format, target: Pictures via this system are quite hard to for me (I think it s my eyesight rather than the system) - however, my methods of using access data are (1) to create a query, and copy that (from the screen where you can see all queries) then paste special in excel...

Excel: Vba Code - Copying Ranges from different Sheets, continuous number, thanks in advance

continuous number, thanks in advance, ogilvy: Sub Main Dim sh1 as Worksheet, sh2 as Worksheet set sh1 = Worksheets( Dados_Contas ) PlaceNumber sh1 set sh2 = worksheets( MySheet ) PlaceNumber sh2 End Sub Sub PlaceNumber( sh as Worksheet) Dim r as Range set r = sh.Cells(Rows.Count, 2).End(xlUp).Offset(1,...

Excel: Visual Basic - Excel Sum by Fill Color, visual basic excel, rcell

visual basic excel, rcell, lcol: Suzanne, The formula as written will give you the sum of the colored cells if the 3rd argument is true or the count of the colored cells if the 3rd argument is false - using the fill color. It can be modified to take a fourth argument that specifies whether...

Excel: VLOOKUP formulas, pivot table, possible solutions

pivot table, possible solutions, aidan: Images dont come out very clearly - so Im happy to see an original file - my email being aidan.heritage@virgin.net. Possible solutions are a pivot table if the data fits taht, or combining the values eg =A1& _ &b1 and then looking up on this combined...

Excel: VLOOKUP or Match Index, vlookup, accurate thanks

vlookup, accurate thanks, employee numbers: I can t tell you if there s an easier way because you gave me nothing to compare it against. How are you doing it now? Do you need to validate all 4 columns? Why not simply do the VLOOKUP in columns B, C, and D and ignore what s currently there? Something...

Excel: Can I have a VLookup Calculated Field in a Pivot Table ?, sales target, relevant targets

sales target, relevant targets, pivot table: I d do this by EITHER determining the maximum width and inserting my formula AFTER this width, or possibly easier, having the pivot table start in column B and put the formula in column A - which won t get overwritten as it s before the start of the pivot...

Excel: Vlookup Error, hardeep, acceptable solution

hardeep, acceptable solution, xls: You need to find a way of getting the first, second, third etc entry- so I m HOPING that a custom function which would use VBA would be an acceptable solution - see http://rapidshare.com/files/255850395/Vlookup_Error_1_.xls for a file that does this...

Excel: vlookup formula with multiple array - IF statement based, invoice data, different company

invoice data, different company, vlookup formula: I don t quite follow -- Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. ----------- I lead free 1-hour Excel webinars on tips & tricks or on What s new in Excel...

Excel: vlookup formula with multiple array, invoice data, zero result

invoice data, zero result, three sheets: Srinivas, Its impossible to write an exactly correct VLOOKUP for you, since I don t know the names of your sheets or the sizes or locations of your data tables on those sheets. [Besides, you said you knew how to use VLOOKUP.] But, to help you, I ll...

Excel: vlookup in pivot table, pivot table, vlookup

pivot table, vlookup, period time: Send a sample wb to me at bobumlas@yahoo.com, use subject of AllExpertsQ , repeat the description of what you want me to help you with. ----------- I lead free 1-hour Excel webinars on tips & tricks or on What s new in Excel 2007: check out http://www.iil.com/webinars/...

Excel: Want to put partial text from cell into another, doe john, jane doe

doe john, jane doe, john doe: Doug You can copy the column with the full names into the third column then use Data Text to columns on the first column whith the space as the delimiter and it will split them into two columns. If you have three names then I would put the full name...

Excel: Week Numbers Over Dates, blank row, application range

blank row, application range, column c: Hi Nik, I saw your question in the Question Pool - not sure if you got your answer already or not, but I thought maybe this might help to get you started. The following code will provide you with the week number of a date. You will just need to work...

Excel: Week number in a month, answer sessions, date systems

answer sessions, date systems, bhavana: Bhavana, The first thing you need to do is to define what it means to be the 1st, 2nd, 3rd, 4th, and 5th week in a month. For instance, if Day 1 of a given month is a Saturday, does that constitute a one-day week, Week 1? And thereafter, is...

Excel: Weight ranking, weight category, column c

weight category, column c, category c: Mike, Column A has the category labels Column B has the rank Column C has the weights then the data is laid out as: A1: A B1: 73 C1: 26% A2: B B2: 85 C2: 25% A3: C B3: 67 C3: 23% A4: D B4: 90 C4: 22% A5: E B5: 44 C5: ...

Excel: Worksheet Link, pivot tables, dalgleish

pivot tables, dalgleish, pivot table: Hi Ana, My fellow MVP Debra Dalgleish has excellent material on Pivot tables on her website, see the index of articles here: http://www.contextures.com/tiptech.html A good place to start is this page: http://www.contextures.com/xlPivot01.htm...

Excel: Worksheet name in formula (Indirect function), cell a1, job

cell a1, job: If the formula you re entering is in column A: =INDIRECT($A$1& !r12c &COLUMN()+4,FALSE) will do the job. If it s in column E: =INDIRECT($A$1& !r12c &COLUMN(),FALSE) in column H: =INDIRECT($A$1& !r12c &COLUMN()-3,FALSE) when you fill right, you ll get...

Excel: workday problem, indicated number, analysis toolpak

indicated number, analysis toolpak, workday: You need to add-in the analysis toolpak (tools, add-ins, then check it - as long as it s not office 2007, then it s already included). You then have access to the workday function which does exactly what you want Returns a number that represents a date...

Excel: XML to Excel Automate process, directory c, xml file

directory c, xml file, vba: You can record the process of dealing with one file - you then need to set it to handle all files in one directory which you can do by reference to this page http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/a450830d-4fc3-4f4e-aee2-03f7994369d6...

Excel: If else, macro code, b2

macro code, b2, ogilvy: Khaja, Are you aware that Left(cells(i,2),2) can never equal RED because you are only taking the TWO (2) left characters. So it should be ====================================== If Left(Cells(i, 2), 3) RED Then ======================================...

Excel: IF ELSE, macro code, b2

macro code, b2, d2: Hi Khaja, First of all, I seem to detect a flaw in your code. Left(cells(i,2),2) can never equal RED because RED is three characters long and you compare that with the first TWO characters in the cell. So after correcting that: If Cells(i, 2) Like...

Excel: IF ELSE, copy columns, macro code

copy columns, macro code, n0: Not sure what s not working except this logic, as I indicated previously: If Left(Cells(i, 2), 2) N0 Or Left(Cells(i, 2), 2) D0 Then WILL ALWAYS be true! Change to: If Left(Cells(i, 2), 2) N0 And Left(Cells(i, 2), 2) D0 Then and...

Excel: IF ELSE, macro code, true bill

macro code, true bill, fundamental problem: Khaja, I don t usually offer macro/VBA advice on this website, but in your case I may be able to help you somewhat. The way to perform an or type condition is to use the OR statement! So you would say: =IF(OR(left(D2,2)= RED ,left(D2,2)=...

Excel: IF ELSE, macro code, b2

macro code, b2, d2: easy enough to do, but your condition will ALWAYS be true as it stands at the moment as you aret testing 2 characters at the left of your cell to match against the word RED - which is three characters. if ucase(left(cells(i,2),3)) RED and ucase(left(cells(i,2),6))...

Excel: If this then that, placement test results, target level

placement test results, target level, target levels: Various ways of doing it, but as you mentioned IF we ll go that way =if(TargetLevel 6,3,if(TargetLevel 4,2,1)) replace TargetLevel with the cell reference - and hopefully other than that reasonably clear - if the targetLevel is greater than 6, then show...

Excel: IF ELSE, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: Use the OR function. =IF(OR(A1= RED ,A1= YELLOW ),ValueIfTrue,ValueIfFalse) 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: xl format a date, excel, date

excel, date: If you only want to type 2 digits, it will not be possible to alter the contents of the cell to make it a date. What you can do is to have a formula like this nearby: =DATE(2009,7,A1) This will return a date in July 2009 corresponding to the number entered...

Excel: 2003vba matching values, cell1, sh2

cell1, sh2, r2m: Bryan, Workbooks( MonthlyTotals ).Worksheets( BAOMT ).Range( A & RW).Value = Range( L & j).Value Workbooks( MonthlyTotals ).Worksheets( BAOMT ).Range( B & RW).Value = Range( M & j).Value Workbooks( MonthlyTotals ).Worksheets(...

Excel: More than 7 Nested IF Statements., d2

d2: You cannot nest more than 7 levels of if statements, but there are often other ways round this - in this instance, you are selecting a value for D2 and returning another value, so vlookup would be one solution - however, in this instance, a much easier solution...

Excel: is 8000 a limit for the Solver?, novosibirsk city, colomns

novosibirsk city, colomns, unpaid volunteer: Your question relates to commercial software that is not part of excel so I m unable to answer the question - it would PROBABLY be possible to write a custom macro to handle the process, you would need the services of a programmer for that - I do write programs,...

Excel: is 8000 a limit for the Solver?, novosibirsk city, colomns

novosibirsk city, colomns, dear bill: Dmitry, I haven t been able to find an answer to this question. The Solver you are using is a third-party tool; if it says it has a limit of 8,000, then so be it. I d suggest that you contact the company who made it for a definite answer. However,...

Excel: is 8000 a limit for the Solver?, linear programming problems, novosibirsk city

linear programming problems, novosibirsk city, colomns: Hi Dmitry, In viewing the website for www.solver.com, I came across the following information that may be helpful for you: Plug-in Large-Scale LP Solver You can combine the Premium Solver and any of three editions of the Large-Scale LP Solver Engine...

Excel: Accessing a closed workbook using Concatenate and EVAL, vba code, xlm

vba code, xlm, account values: I don t think it s VBA so much as excel - it sounds as though you may be using an indirect worksheet function which definitely won t work - BUT as you are using VBA, I would get the VBA code to open the worksheet and get the value - indeed, as it s doing it...

Excel: Adding, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: In Excel 2007, right-click on the Status Bar and make ssure SUM is checked ON. 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: Adding Commas, free microsoft excel, cell b2

free microsoft excel, cell b2, question sign: Use the SUBSTITUTE command: =SUBSTITUTE(A1, , , ) This will replace the spaces in A1 with commas. 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: Adding a Constant value to Time in Excel, cell a1, cell b1

cell a1, cell b1, cell a2: Hi Richard, If I am understanding your question correctly, I believe that this solution will help. Say you have the start time in cell A1 and the increment in cell B1. I am assuming that this is where you would enter these two values each time. Please...

Excel: Adding rows in a protected worksheet, cells, job

cells, job: Not familiar with Max excel, but in PC excel, when you protect a sheet you have the option of still allowing rows to be inserted. If that option doesn t exist on the Mac you d have to use a macro which would unprotect the sheet, insert the row(s), reprotect...

Excel: Aggregating, account values, alternate approach

account values, alternate approach, dalgleish: Reagan, Excel can already do this for you. You can create a pivot table (it is under the data menu in Excel 2003 and earier and on the left of the insert tab in the ribbon in xl2007). You would make Customer a row field and account value would...

Excel: Append Worksheet to Summary Sheet, b cells, blank row

b cells, blank row, data sheet: If this is a one off, I d do it manually, if it s an ongoing process then presumably data may already exist - so what has to happen here? The basic macro is simple enough - if a one off, copy all data from data A to combined data, find the first blank row...

Excel: Arithmetic Operations In A String, excel, evaluate

excel, evaluate: You can use an old Excel 4.0 macro function for this. The reference is here, on the Excel tips and tricks section: http://www.ozgrid.com/News/excel-evaluate-formula-VBA.htm From that site, here are the step by step instructions: 1) Select cell B1 2)...

Excel: How to Arrange Data from one Sheet to Another Using VBA ? (From Columns to Row), vba code, input sheet

vba code, input sheet, job2: The basic macro is simple enough, BUT I cannot follow the logic - mainly I suspect due to the layout I see at AllExperts - it would seem to be for each record on the main sheet. X=2, Y=3 If the data (BUT WHAT DATA) remains the same then output to Row...

Excel: How to Arrange Data from one Sheet to Another Using VBA ? (From Columns to Row), vba code, input sheet

vba code, input sheet, job2: Nadeem This is the way I would solve it. 1 Sort the data by MOP then by contract then by job. 2 Assuming the first column is contract and in column A--add the number of columns that you expect to have jobs for one particular unique contract and mop...

Excel: How to Arrange Data from one Sheet to Another Using VBA ? (From Columns to Row), vba code, output cells

vba code, output cells, therow: Sub Rearrange() Dim n As Integer, TheRow As Integer, TheCol As Integer, i As Integer, Cur As Worksheet On Error Resume Next Set Cur = ActiveSheet Application.DisplayAlerts = False Worksheets( OUTPUT ).Delete Worksheets.Add.Name =...

Excel: Assistance in excel, a10, b10

a10, b10, cells: Hi gvkrishnan, For your first question, you can use the following formula: =SUM(IF(MONTH(A1:A10)=4,B1:B10,0)) You will just need to modify the range to suit your worksheet. Also, the 4 is in reference to the month April . You can modify that as needed....

Excel: Automatic Data population, cell c5, lookup reference

cell c5, lookup reference, cell references: Jax, What you are saying isn t possible, unless you have the calculation method set to manual. If you have a sheet named MASTER (rather than sheet 1, as this followup confusedly says), and cell B4 on the MASTER contains 26, then the formula...

Excel: Automatic Event, excel2003, data application

excel2003, data application, excel worksheet: Since you are pulling in data then you should be able to use the Worksheet_Calc method to have the print job run at the interval ou need using the code I provided. Private Sub Worksheet_Calculate() End Sub or try Private Sub Workbook_SheetCalculate(ByVal...

Excel: Automatic population of a cell, automatic population, data validation

automatic population, data validation, cell population: Mittun, The solution depends on if the user has TWO cells in which to enter either a host name or an IP, or only ONE cell. I suppose it is TWO cells, one for the IP and another for the Host name. All you have to do to get the answer to appear...

Excel: AVGIF, excel, array formula

excel, array formula, average: The formula that you have is right, you just need to enter it as an array formula. So type the same as you have: =AVERAGE(IF(A1:A5 0, A1:A5, )) And enter it with Ctrl+Shift+Enter. That way it will only take the negative numbers into the AVERAGE formula....

Excel: Average Amount of Product Used, logical formulas, two tables

logical formulas, two tables, widgets: Randy, The best way to do this is to keep two different tables, one for USEAGE and one for ADDITIONS. Then you can find the average of the USEAGE table. You could use these two tables to create a third, ACTUAL INVENTORY, which would be the combination...

Excel: Averaging data, example benzene, countif

example benzene, countif, sumif: You didn t specify why this had to be done in VB - I d do it with a pivot table as that would do the job in one hit. IF it has to be done in VB or any other formulaic method, then it s basically a countif and a sumif - both of which exist in VBA as worksheetfunction....

Excel: Averaging populated cells, array formula, sampling dates

array formula, sampling dates, ogilvy: Pete, You din t alter the formula quit correctly. I used =AVERAGE(OFFSET($H$1,LARGE(IF(ISNUMBER($H$10:$H$374),ROW($H$10:$H$374)),3)-1,0,365,1)) I entered through Jan 4 and it returned 1.33 (1 1 2) I then entered the date for Jan 5 and it returned...

Excel: array question, single quote, h7

single quote, h7, ogilvy: Joseph, Having those cells formatted as text are the source of the problem. You can enter your values as 7-4 and not format the cell(s) as text. The single quote is considered a formatting character by Excel and the other characters are treated...

Excel: New question., porcelain china, countif

porcelain china, countif, product 01: Ray, I m sorry but the finer points of your new question elude me. Your image might help, except the resolution is so low that I can t read any of the text you so carefully wrote by way of explanation. Even when I zoom in on the image its fuzzy. ...

Excel: auto numbering unique names, unique names, header value

unique names, header value, ogilvy: Nick Assume the first nick is in A2 and the rest below, sorted as you show. then in B2 put in the formula =if(A2 A1,b1+1,B1) Now select B2 and drag fill down the column. If your list does start in row 1 with no headers, then put a 1 in B1 and...

Excel: automatically moving data to another sheet, target value, target address

target value, target address, copy worksheets: Jessica, Right click on the sheet tab where you will be entering date and select view code. in the resulting code module, from the left dropdown at the top select worksheet and from the right dropdown select change (not selectionchange) that will...

Excel: Balance per month, selection functions, month of august

selection functions, month of august, current date: Peter, To find the current month of NOW() or any other date, simply use the MONTH(cell) function, where the cell contains a valid Excel-type date serial number. Such as, Month(A1) where NOW() is in cell A1. You could also use =MONTH(NOW()), but this...

Excel: BUDGET, lloyds bank, cell reference

lloyds bank, cell reference, saving accounts: IF I understand what you are asking then use an absolute cell reference in your formula. So, if you type =A1+$F$1 in C1, for example, and copy it down one row then that cell have =A2+$F$1 as its formula. Copy it to D1 and that cell will have =B1+$F$1 as...

Excel: Calculate sums of random numbers, dim mark, mark case

dim mark, mark case, rng: Tim, You are looping 10 times per cell (so your are generating 100 numbers) - I assume you only want to generate 10 numbers in cells A1 to A10. this worked for me: Public Sub CommandButton1_Click() Dim rng As Range, i As Integer Dim mark, sumFail,...

Excel: Calculations, absolute references, relative reference

absolute references, relative reference, pivot table: Suzette, Use the SUMIF function. SUMIF can take two ranges and a criterion value. =SUMIF(range-to-check-for-criterion,criterion-value,range-to-sum) You need to create a list of unique names where the names appear only once. (If you don...

Excel: Calculations, array formula, cell approach

array formula, cell approach, excel works: Ron, That is the way Excel works. Formatting just determines how the cell is displayed. Underneath, the value in the cell hasn t changed, and when you do your sum, it uses the value; it doesn t use the displayed amount. There are two ways you can...

Excel: Calculations in Excel, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: This sounds like a rounding error. Make sure you re showing enough digits after the decimal points in your column. 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...

Excel: Cell above for formula, indirect address, hi matthew

indirect address, hi matthew, b13: Hi Matthew, If I am understanding your question correctly, you want to find the number of days between today and the date in the cell above. If this is correct, please try this formula: =TODAY()-INDIRECT(ADDRESS(ROW()-1,COLUMN())) I hope this answers...

Excel: Cell Shading, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, cell shading: 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: Changing Chart Based on Value Selected, date selection, indirect method

date selection, indirect method, aidan: You can do it with indirect, but you would EITHER need to use VBA to reset the chart range OR have a charting range within the worksheet that picked up it s data based on the selection. From the description, it does sound as though sheet2 is the charting...

Excel: Charting data, numerical division, displaying your data

numerical division, displaying your data, priority level: Margaret, You may have already solved this by now, but in case not, here is a response that may (or may not!) help. First of all, I d suspect your question should read: ....I want the chart to have the hit and miss for each DIVISION displayed...

Excel: Charts, sheet tab, single store

sheet tab, single store, answer right: Sorry, I took the reference to pages and sheets to refer to worksheets for pages and workbooks for sheets. IF you are moving the data to multiple locations within one sheet, then you are pretty much going to have to do it as you are doing at the moment -...

Excel: COUNTIF, sumif, ogilvy

sumif, ogilvy: sharmane, assume your data starts in row 1 and ends in row 204, then =sumif(I1:I204, 40 ,F1:F204) should give you the total of the prices in column F if the age is less than forty in column I. Change the 204 to reflect the last row with your...

Excel: Code for Upper Case in excel, proper case, case change

proper case, case change, upper case: dim cell for each cell in range( A:A ) if len(cell.value) 0 then cell.value=ucase(cell.value) next gives upper case - change Ucase to lcase for lower case There isn t any direct function for Proper case, but for each cell in range( A:A ) ...

Excel: Column headings, excel, dates

excel, dates, freeze panels: 1. You can use the menu Window- Freeze panels for that. Select a cell, go to that menu option, and the cells to the top and left of that cell will always be visible for that sheet. 2. You can check the custom lists available on the menu Tools- Options- Custom...

Excel: Combinations, credit card statements, statement period

credit card statements, statement period, mehta: It is possible, and it s a fairly common question - so rather than re-inventing the wheel, I ll refer you to http://www.tushar-mehta.com/excel/templates/match_values/index.html where there is a discussion about this that HOPEFULLY will provide what you...

Excel: Command in a Macro to Select a Cell, value contents, m11

value contents, m11, macro recorder: Stan, Cells(rows.count, G ).End(xlup).copy cells(rows.count, M ).End(xlup).PasteSpecial Paste:=xlValues should do it. if you need to work with either of those locations again in the same code, then use a reference Dim rG as Range, rM as Range...

Excel: Compare part of strings in a table against a list of Keywords, master reference, keyword list

master reference, keyword list, great solution: Nagesh, This is a very complex question, but some time ago another questioner gave me a solution which worked just like you describe! I haven t been able to find his result, yet, but I have it somewhere. if you still need an answer to this, PLEASE...

Excel: Comparing and Sort/Extracting Data, error prevention, excel function

error prevention, excel function, customer number: Jef, The best way is to use VLOOKUP to find the matching customer name from one table to the other. Once you know how to find the matches across tables, you can lookup information from one table into another. You can either build a new third table...

Excel: Completing a contract database, negotiation stages, database range

negotiation stages, database range, apologies in advance: Ollie, lastrow = worksheets( Database ).cells(row.count. B ).End(xlup).row + 1 Sheets( New Contract ).Activate Sheets( Database ).Range( B & lastrow &:F & lastrow).Value = ActiveSheet.Range( D5:H5 ).Value Sheets( Database ).Range( G & lastrow).Value...

Excel: Conditinal Formatting, room booking system, mutiple areas

room booking system, mutiple areas, excel 3000: Hi James, You could probably achieve the solution you want by creating a macro, but I was not sure how comfortable you are with using macros? Also, there is a shareware program that merges cells that is available at download.com: http://download.cnet.com/Merge-Cells-Wizard-for-Excel/3000-2077_4-10289817.html?tag=mncol...

Excel: Conditional Formatting, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, question sign: To the best of my knowledge, you can only use the built-in color and icon sets in Excel with conditional programming. The exception, of course, is if you code something yourself using VBA. Or… you could use an IF statement or a VLOOKUP and insert your...

Excel: Conditional Formatting - Training Matrix, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, 599cd: See this tutorial. Perhaps it will help you: http://599cd.com/tips/excel/extend-conditional-formatting?key=AllExperts 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: Conditional formating, cell a2, cell a1

cell a2, cell a1, please follow these steps: Hey Ian, Please follow these steps to solve your problem: 1 - Select cell A2 2 - Click on Format on the top menu, then Conditional Formatting 3 - Select Formula Is in the first dropdown 4 - In the next box type this formula: =ISBLANK(A1) 5 - Click...

Excel: Conditional formatting, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, allexperts: Sure, you could use VBA to read the format of one cell and use it to change the format of another. I don t have the code handy (nor know it off the top of my head) but it is certainly possible. Let me know if you have any other questions or comments. ...

Excel: Conditional formatting, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, vba code: You re going to need some custom VBA to accomplish this. You could EASILY just create a third column that would show a value if the first two columns didn t match. Just use a simple IF function: =IF(A1=B1, , X ) That would put an X in C1 if A1 was...

Excel: Convert Text to Number, mobiletel, column c

mobiletel, column c, allexperts: Ross, I provided a change event in response to the question you cite. I only adapted the code the questioner provided to work as an event. I can t say what it actually does and I would be surprised if it does what you want. Do you want a macro where...

Excel: Converting my formula to calculate time (Overtime spreadsheet), excel, time

excel, time: If the number is in time format, you may need to convert it to hours first. You can try with something like: =IF(I25*24-40 0,0,I25*24-40) This will give you the hours, but they cannot be formatted as Time directly. If you want the result to be formatted...

Excel: Converting Letters, lt 1, question option

lt 1, question option, code structure: Function MyCode(rg As Range) As String Const Codes As String = ABCDEFGHIJ ========change according to your code scructure (In my excel file, I have already changed it according to our code structure) Const Corresp As String = 1234567890 ========each...

Excel: Converting Letters, lt 1, excel spreadsheet

lt 1, excel spreadsheet, instr: It s a lot easier to use a user-defined function. Press Alt/F11, use Insert/Module, copy this in: Function MyCode(rg As Range) As String Const Codes As String = HIGABCDEFJ ========change according to your code scructure Const Corresp As String...

Excel: Copy Cell Content to another cell based on the Status of another cell in the same row, relevant record, cell content

relevant record, cell content, status column: Varun, The portion of your question which makes it very difficult is this: [I have abbreviated your question and emphasized the significant portion] I would like to .... copy the order# and paste it .... WHENEVER A USER selects the status as...

Excel: Copy Cell Formatting Using Vlookup, master timetable, staff column

master timetable, staff column, vlookup function: I presume the colour information is more complicated than would be allowed by conditional formatting, so yes it would use VBA - I wouldn t use the match function, I d simply check each cell in the range and if it s value was the value you wanted then the data...

Excel: To Copy File And Folder Names, folder object, folder names

folder object, folder names, filesystemobject: I cannot get the code as copied to run, which makes it difficult, but as far as I can tell the important bit is If FullPaths = True Then Dest.Value = OneFile.Path Else Dest.Value = OneFile.Name End...

Excel: Copy Rows of Data Into Master Sheet, master spreadsheet, excel vba

master spreadsheet, excel vba, xls extension: Rhonda, I tested this and it worked for me - at least it worked as I expected. I assume any files with an .xls extension in one of the subdirectories is to have the first sheet in the workbook copyied to a sheet Named Data in Master.xls in the next available...

Excel: Copy From One Sheet To Another, pastes, ogilvy

pastes, ogilvy, thanks in advance: Tom, If you insist on a macro, then I can t help you. I don t offer macro/VBA advice on this website. You ll have to ask an expert who does not say in their profile (as mine does) , NO MACRO QUESTIONS!, but rather, look for an expert who say he has...

Excel: Copying Cells to a close workbook, run time error, sheet cells

run time error, sheet cells, template sheet: Steven DATABASE_RECORDS = DATABASE_SHEET.Cells(Rows.Count, A ).End(xlUp)(2) should be Set DATABASE_RECORDS = DATABASE_SHEET.Cells(Rows.Count, A ).End(xlUp)(2) I tested this and it worked for me: Private Sub CommandButton1_Click() Dim...

Excel: Copying cells to a closed workbook, template sheet, documents and settings

template sheet, documents and settings, b count: Steven, Sorry - I guess I got myself confused when I was restructuring the code. Template_Sheet is a reference to the sheet, not a variable holding the name of the sheet which is what my code would need. So it should be Template_Sheet.Range( c12...

Excel: Copying Data and Eliminating Blanks, selection functions, record selection

selection functions, record selection, autofilter: Russell, There is nothing like over-simplification to make a question (like your original) impossible to understand. Now I get it. The first thing to do is fix your data table so there are no blanks in column A, where the name is. Do do this,...

Excel: Copying Data from sheet to sheet based on condition, target value, type mismatch

target value, type mismatch, cells: Your code is wanting a single cell to be selected, but when copying and pasting multiple cells are in the selection. In this instance, the QuICKEST and easiest fix is a catch-all error trap - make the first line of the code (after the sub...) On Error...

Excel: Copying data speci, input flow, inputbox

input flow, inputbox, excel sheet: Would the date column always be in a given location, or would the macro have to work out which column to use? - the process is easy enough to write, indeed it s fairly easy to do manually - data, filter, autofilter, select the date column, choose custom and...

Excel: Copying formulas with an odd increment, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, cell reference: If you create enough cells so that Excel can see your pattern, you should be able to AUTOFILL them down. For example, create A1 through A6 which should be enough. Now HIGHLIGHT A1 through A6 and then AUTOFILL down. I just tested it, and it worked fine for...

Excel: Count Cells Containing a Date in the Past, count cells, array formula

count cells, array formula, katie c: Katie, Just to add - if you have Excel 2007, Microsoft added a new function in that version called COUNTIFS (with an S on the end) that allows multiple criteria: =Countifs(Katie!C:C, &$B4,Katie!D:D,$C4) ==================== end of revision ==============...

Excel: Count of the number of formatted cells, free microsoft excel, conditional formatting

free microsoft excel, conditional formatting, excel tips tricks: I don t believe that any built-in function like COUNTIF will tell you the color properties for a cell. You d need to use VBA code for that. Here s a tutorial I wrote about conditional formatting that will show you how to set the color info in a cell. It s...

Excel: How to Count the unique cells from a set of Duplicates , using VBA ?, john walkenbach, chip pearson

john walkenbach, chip pearson, contract name: Nadeem, You don t need code. See the formula on this site: (Chip Pearson s site) http://www.cpearson.com/excel/Duplicates.aspx see the section: Counting Distinct Entries In A Range if you want code, use the code at this site: (John Walkenbach...

Excel: How to Count the unique cells from a set of Duplicates , using VBA ?, mail id, contract name

mail id, contract name, richard florida: Nadeem Unfortunately the COUNTIFAND function doesn t work in excel, so what I usually do is combine my criteria and then count the occurences of the combined criteria. Your problem has another twist in that you want to count only the unique occurences...

Excel: How to Count the unique cells from a set of Duplicates , using VBA ?, table contract, contract name

table contract, contract name, code id: Your explanation is a little hard to follow, and I’m not 100% sure why this needs to be done via code – I’d be INCLINED to get the code to generate a pivot table (or refresh an existing one) which would make the grouping a lot easier – the code could work...

Excel: Countifs, cell d1, cell c1

cell d1, cell c1, array formula: Say that in A1:A10 you have the Male/Female indicator, each of these cells contains either the letter M or the letter F. In B1:B10 you have the corresponding ages. In cell D1 (or any cell), type: =SUM(IF(A1:A10= M ,IF(B1:B10 29,IF(B1:B10 36,1,0),0),0))...

Excel: Counting comma delimited items in numerous cells in one column against another identifier (zip code) in another., comma delimited, array formula

comma delimited, array formula, relational tables: Well, you could use the FIND function to see if each search term is in each description… but you d have to do one column for EACH term. Is there ANY way I could talk you into redesigning this as a database? You d need one record per need but in the end,...

Excel: Creating a Graph in a new tab without values, creating a graph, chart wizard

creating a graph, chart wizard, move option: Kevin, As you work through the Chart Wizard in Excel 2003, you ll see an option to Insert the chart you re making on the same page or on an entirely new page. Select the entirely now page. If you are using Excel 2007, after you insert the chart,...

Excel: Creating Hyperlinks to Folders, creating hyperlinks, logical names

creating hyperlinks, logical names, folder name: You will have to write a macro and even then the writing of the macro may not be as quick and easy as it could be depending on how the folders are arranged, how they are arranged, their logical names, if applicable, etc. You may have to write 100 lines of...

Excel: Cumulative total, cell b1, vba macro

cell b1, vba macro, stock purchases: Juergen, All you have to do is create a simple formula in column B which adds the number in the cell above where the formula is, to the number in the cell to the left of where the formula is. If you type this formula in cell B9, it will look like: ...

Excel: Custom menus in 2007, flyout menu, custom menus

flyout menu, custom menus, macro language: I’m not sure what you mean by flyout menu, and I’m slightly worried by the use of XL4 language, which I take to be the macro language from Excel 4 – which was superceded in Excel5 by VBA (which would still be compatable) – if I could see the file I might be...

Excel: How to calculate ratio in excel?, cell references, excel 2000

cell references, excel 2000, gdp: not sure what you mean by GDP feature, but I assume by ratio you mean one value divided by the other - at which point, that is exactly what the formula is =FirstValue/SecondValue where FirstValue and SecondValue relate to the cell references you want...

Excel: calculating Voids on a weekly basis using Excel 2003

Kerith, I noticed your comment but you can write a followup to ask for clarification, instead of sort-of asking a question in the comment area. All you have to do is create a cell whose contents LOOK LIKE the text string that a criteria would look...

Excel: calendar, mr abc, ms alexander

mr abc, ms alexander, oracle 9i: Bhavana, Your question is a little difficult to understand because the attempt you made to create a sample of your data within the question has become all scrambled. I can t tell what the data is, what column its in, or what. Attaching an image of...

Excel: cell input & alphabetization, target address, sheet tab

target address, sheet tab, controls group: XL 2003: Right-click any toolbar button, select Forms, click on the button icon and draw a button onto the worksheet, when you let go of the mouse for drawing you will see a dialog about which macro to run, so point it to MoveIt (from the macro you put in...

Excel: changing direction, move selection, linear fashion

move selection, linear fashion, excel spreadsheet: David, In excel 2003 and earlier, you would go to tools= Options and in the Edit tab it has an entry Move selection after Enter You have two choices. DOWN and RIGHT In Excel 2007, if you click the Office button, at the bottom of the Frame should...

Excel: combining data in Excel, cell a2, cell b2

cell a2, cell b2, cell a1: Example: on Sheet1, cell A1, is the heading Customers . Below it, beginning in cell A2, is the list of Customers. On Sheet2, cell A1, is the heading Materials . Below it, beginning in cell A2, is the list of Materials. On sheet3, cell A2, enter: ...

Excel: compare dates and conditionally format, logical statement, last date

logical statement, last date, spreadsheet: Chris, From the title of your question, it sounds like you already know how to do this: use Conditional Formatting. I can t give you a complete tutorial on conditional formatting here; I wonder what aspect of the application of Conditional Formatting...

Excel: comparing content in a cell range, e mail addresses, excel 7

e mail addresses, excel 7, dear bill: Elke, Use COLUMN C to create a COUNTIF equation. Each COUNTIF will take as input the adjacent email address from column A, and will count the number of times it occurs in column B. Any row containing a COUNTIF value 0 has an email address from A...

Excel: comparing and deleteing multiple rows and columns, autofilter, column c

autofilter, column c, header data: Brad, first copy the sheet and work on the copy. Assume your data is in Column A, starting in row 2. Assume the unique identifier for the contact (whatever it is) is in column A as well I would insert two new columns then I would go to the new...

Excel: comparing two excel spreadsheets, excel spreadsheets, spread sheets

excel spreadsheets, spread sheets, multiple days: I don t understand the part about identify a donation... There s no really good way to compare spreadsheets unless they re truly practically identical. Best bet: on a 3rd sheet cell A1, enter: =IF(Sheet1!A1=Sheet2!A1, , X ) and fill right & down. Wherever...

Excel: complex formula, aidan, attachments

aidan, attachments, heritage: Would you like to be a little more specific please - I have nothing here to base an answer on other than =if(Yourtest=true, Decrease , Increase ) where yourtest is whatever formula you want to use to evaulate the decrease. Should it help, you can...

Excel: conditional formating for dates, conditional formatting, lol thanks

conditional formatting, lol thanks, formating: select all the cells with dates that are to take on this formatting. Conditional formatting has up to 3 conditions you can enter. Use Format/Conditional Formatting, change Cell value Is to Formula Is , assuming A1 is the ACTIVE cell, put in this formula...

Excel: conditional formatting, conditional formatting, blank cell

conditional formatting, blank cell, attis: The problem as I see it is that your duplicated rows could presumably occupy both an odd number or an even number of rows, so the original condition would not work alongside the newer one. I would be INCLINED to do this with a formula in a blank cell, which...

Excel: conditonal formatting, array formula, array formulas

array formula, array formulas, conditional formatting: Not sure I understand now. Row 1 should be one color, row 2 should be a different color, row 3 should be another different color? How many rows are to be used to then determine WHICH row gets highlighted? Give me some specific examples of what should be...

Excel: convert four-digit number to military time, little known fact, military time

little known fact, military time, time int: Virginia, You need to break the number in half (Hours & minutes), and then use the TIME() function to reassemble the halves into a serial number that Excel recognizes as a time. Start by dividing the number by 100. In your example, you ll...

Excel: To copy file and folder names, increment letter, xdirect

increment letter, xdirect, folder names: Probably not the code I d use, but Sub GetFileNames() Dim xRow As Long Dim xDirect$, xFname$, InitialFoldr$ xRow = 2 InitialFoldr$ = c: emp Startup folder to begin searching from With Application.FileDialog(msoFileDialogFolderPicker)...

Excel: copy to new worksheet and keep formulas?, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: My apologies. I probably should have stated that you have to have that file OPEN in the current Excel session. Open your source file. In the SAME Excel Window, click on Office Open and open the destination file. Now go back to the source file and...

Excel: copy range between 2 workbooks, change column

change column: Make the changes as indicated, if necessary: Sub CopyCol6() wb1= Workbook1.xls ====change name wb2= Workbook2.xls ====change name CopyCol = 6 ====change column For i=1 to 10 SheetName = Workbooks(wb1).Sheets(i).Name...

Excel: cost comaprison worksheet, horizontal bar graph, colc

horizontal bar graph, colc, color choices: Nick, How do you want the difference in price to be color-coded? Do you mean that if vendor 2 vendor 1, you want to see one color, and if Vendor 1 vendor 2 you want to see a second color, and if they are the same you want to see a 3rd color? ...

Excel: countif not working, true values, double quotes

true values, double quotes, good solution: Amy, You ve come up with a real puzzler. It seems like what you ve done should work and it doesn t. I haven t been able to make it work either! Excel must be getting confused, because both the and the % characters are OPERATORS. Excel seems...

Excel: creating a total hours column, monday tuesday wednesday, array formula

monday tuesday wednesday, array formula, blank cells: Joseph, Assume the values are in B7:H7 then =SUM(IF($B7:$H7= off ,0,CHOOSE(MATCH($B7:$H7,{ 7-4 , 4-9 , 12-6 , 6-2 },0),9,5,6,8))) entered with Ctrl+shift+enter Add any additional possible shifts to the list inside the brackets {} and the corresponding...

Excel: cross reference 2 sheets, excel spreadsheets, vlookup function

excel spreadsheets, vlookup function, matching company: Steve You can use the VLOOKUP() function to find the matches and isolate those that do match. If I am reading correctly, you want to match the company name in column A of sheet 2 to the company name in column A of sheet 1, and if there is a match you...

Excel: cutting and pasting, hot key, hotkey

hot key, hotkey, cells: Any form of hot key would probably take longer to do than using the mouse - right click on the letter at the top of the column and select cut, then right click on the letter at the top of the destination column and choose insert cut cells - thats two clicks...

Excel: Data Entry, sheet tab, data entry

sheet tab, data entry, wb: If it s in the same cell as the main sheet, you can right-click a sheet tab, select Select All Sheets , and then whatever you do in one sheet will be done in all the sheets. When you re done, right-click the sheet tab & select Ungroup Sheets If it s...

Excel: Date Calculation, cell a1, cell b1

cell a1, cell b1, spreadsheet: Hi Denise, If the date is in cell A1, and the shipping date is to be in cell B1, you would need to enter this formula in cell B1: =A1-30 Excel will automatically calculate 30 days prior using this formula. You can drag this formula down the entire...

Excel: Date change in Excel, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, looking for a new job: I haven t used Windows 7 yet, however, it sounds like something that you d set in the REGIONAL SETTINGS for Windows. 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...

Excel: Date change in Excel, excel function, excel sheet

excel function, excel sheet, win 7: Stig, This obviously has something to do with the new O/S you installed. It s probably NOT an Excel function, but there may be a setting in the OPTIONS box for international settings. Hunt around in the OPTIONS box (found from the Office Button in...

Excel: Date Range match, digit number, relevent

digit number, relevent, l5: a date in July 2009 is ACTUALLY a 5 digit number in the 40,000 range from memory- as your dates are only this year, you could use the MONTH worksheet function =if(month(YourCell)=7,YourMatch, ) would be the sort of syntax to use - I hope this helps,...

Excel: Date & Time Difference, timecell, optional range

timecell, optional range, days between two dates: I m not sure about your maths - 6pm to 1pm is 19 hours - but apart from that the basic method should work fine - see http://www.aidanheritage.byethost3.com/excel/Networkdays.xls where I have created a formula for you - to keep it vaguely clear this doesn...

Excel: Decimal Places, file open dialog box, csv file

file open dialog box, csv file, keyboard shortcut: No, not without seeing the csv file. But, you can try opening the workbook using Workbooks.OpenText Filename:= method. This should give you a little more control over the file and then since a workbook is now open you should be able to do the converting...

Excel: Delete entire column if one cell in the column meet conditions, count column, circular reference

count column, circular reference, ogilvy: CK, yes, the range should have been =countif(A2:A5000, *ID* ) A simple typo. If you want a macro, then you should state that as I say in my profile. I have no idea what you want unless you tell me Sub DeleteColumns() Dim r As Range, i...

Excel: Delete Row with Specific keywords, sirajuddin, rng

sirajuddin, rng, macro code: Sirajuddin, Sub DeleteRows() Dim rng As Range Do If Not rng Is Nothing Then rng.EntireRow.Delete End If Set rng = Cells.Find(What:= china , _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext,...

Excel: Deleting drawing object using VBA, rectangle, shapes

rectangle, shapes, drawing: I m not sure I d want to do this with VBA, but ActiveSheet.Shapes( Rectangle 1 ).Delete where Rectangle 1 is the name of the shape, would delete the shape - BUT putting a new shape in place would then cause it to have a new name, so the macro would not...

Excel: Dependent list with criteria, choice c, option c

choice c, option c, columna: Kostas, There is a method to make dependent drop down lists, but I don t think it can be done the way you want. For each choice you make in C1, you have to precisely define the dependent list, by creating the dependent list in cells somewhere. I don...

Excel: Drop down list from another sheet, data validation, dalgleish

data validation, dalgleish, menu structure: Klay, Using data validatkion, using the list option? go to Insert= Name= Define Name: List2 Refers To: Sheet5!$A$1:$A$20 Click the add button. When you choose List for the type of validation, in the resulting text box put in =List2...

Excel: Droplist, combobox, ogilvy

combobox, ogilvy: Anas, You can use formulas to remove duplicates and built the source for the combobox. Then when you add items you won t have to do anything additional. If you want to send me a sample workbook I can show you how to do it or send me your email address...

Excel: date, conversion algorithm, excel function

conversion algorithm, excel function, next five years: Wolfgang, Well, you may see it that way, but that doesn t mean the creators of Excel provided the inverse WEEKNUM function, because they didn t! Nor did they write a DATE() function variation which uses the WEEKNUM value instead of Year, Month and...

Excel: definition, floating point numbers, vba code

floating point numbers, vba code, variable declarations: Dave, The single and double identify the data type of the variable and how much storage (in terms of memory) needs to be set aside to store the values assigned to the variables. Dim means to dimension the variable. What you show are called variable...

Excel: dependent validation lists with dynamic ranges, cell b2, data validation

cell b2, data validation, cell a2: 3rd time s the charm :-) one minor adjustment, sorry! Fill corrected answer here: Suppose the range with the kids names is named Children . Also, suppose C2 contains the dropdown you want to be able to edit . First, in the Error Alert tab of the...

Excel: dependent validation lists with dynamic ranges, cell b2, data validation

cell b2, data validation, dynamic ranges: Hi Hilde, I think that the dynamic ranges you refered to will help you in your solution. If you use a dynamic name range, the user will be able to add to the name list and the dropdown box will be updated automatically. 1 - Click on Insert on the...

Excel: dependent validation lists with dynamic ranges, cell b2, data validation

cell b2, data validation, cell a2: Hilde, You ve created a very brilliant cross-coupled pair of validation lists! I ve never seen that done before, it s fantastic. It seems that the only problem you have is how to make the third drop down editable. I m sure you ve gotten the...

Excel: different coloum how to protect, gray rectangle, coloum

gray rectangle, coloum, column letter: Suresh Select the entire worksheet by clicking the Select All button (the gray rectangle directly above the row number for row 1 and to the left of column letter A). Click Cells on the Format menu, click the Protection tab, and then clear the Locked check...

Excel: discount, homework question, homework problems

homework question, homework problems, sorry bill: Gail, We re not supposed to hand out the answers to homework problems on this site. I tried to give you a hint as to what to do, without handing you the answer on a silver platter.... so you d learn something. I didn t; mean to be snotty... sorry!...

Excel: drag number with substract, substract, hyphen

substract, hyphen, 000000: Hi Ming, In the first field type the following formula: =TEXT(ROW(A16622), 000000 )& -2009 What this does is sets a starting number and the format, but keeps the hyphen and the following 2009 the same. Drag this formula down to auto fill and...

Excel: dragging indirect formulas down the page, sheet cells, indirect sales

sheet cells, indirect sales, page hi: Andrew: I must admit I m confused by why you would want to design your formula the way you did (I think I would need to see your spreadsheet to fully understand#; however, I believe I have a solution to do what you are asking: =IF#Sales!$J3 0,INDIRECT(...

Excel: drop down list, list of states, parag

list of states, parag, drop down list: Parag, Since this is a fairly long topic to describe, let me refer you to Debra Dalgleish s site where she has written out and illustrated two ways to do it. http://www.contextures.com/xlDataVal02.html http://www.contextures.com/xlDataVal15.html...

Excel: How to Make duplicated column data into a Row using Excel VBA?, excel vba, table contract

excel vba, table contract, contract name: Nadeen: I am having a little difficulty understanding what you want and need. Please send me a copy of your file to my personal email address along with another attempt of explaining what you need and try to include a sample of what you want the macro...

Excel: How to Make duplicated column data into a Row using Excel VBA?, excel vba, table contract

excel vba, table contract, contract name: Your explanation is a little hard to follow, and I’m not 100% sure why this needs to be done via code – I’d be INCLINED to get the code to generate a pivot table (or refresh an existing one) which would make the grouping a lot easier – the code could work...

Excel: dynamic data ..., dynamic charts, cell reference

dynamic charts, cell reference, track eight: Sandy, Yours: =SUM(((INDIRECT($A$1 !$H$18:$H$500 = Foundation ))*(INDIRECT(A$1$ !!$M$18:$M$500 = Cash-In ))*((INDIRECT(A$1$ !$I$18:$I$500))) Mine =SUM((INDIRECT(A1& !$H$18:$H$500 )= Foundation )*(INDIRECT(A1& !$M$18:$M$500 )= Cash-In )*(INDIRECT(A1&...

Excel: Email Notification To Track, target range, mitem

target range, mitem, outlook application: When EXACTLY do you want the email to be sent? When the workbook has been saved or when data has been changed regardless if the workbook gets saved? On reason why it is not working is that no email address has been provided/typed in the code. To make...

Excel: EXCEL 2003 array formula, boa c, array formula

boa c, array formula, insurance payments: DJ, this worked for me in Excel 2003: =SUM((CCCU!$B$14:$B$2316 =39904)*(CCCU!$B$14:$B$2316 39934)*(CCCU!$C$14:$C$2316=21)*CCCU!$E$14:$E$2316+(BOA!$B$14:$B$2316 =39904)*(BOA!$B$14:$B$2316 39934)*(BOA!$C$14:$C$2316=21)*BOA!$E$14:$E$2316) Personally...

Excel: EXCEL, coloum, column feature

coloum, column feature, width option: tathagata The text to column feature should work very well for you. First paint the cells that you are working with, then go to toolbar/data/text to columns A pop screen will appear--on that screen select delimited then select next. On the next screen...

Excel: EXCEL Text Lists, data validation, hlookup

data validation, hlookup, blank rows: Clint, This has been a tough one to figure out but I took another look and an idea hit me... at 3 AM! What you want to do try to find each name from the master list in the calendar itself, and list the names that fail to be found. That s just...

Excel: Excel 07 Compare cells, c11, b11

c11, b11, a11: =IF(A3:A11=C3:C11, X ,IF(B3:B11=C3:C11, Y , Z )) Enter the formula as an ARRAY. That is, after entering this formula into a cell, instead of hitting the Enter key, hit Ctrl-Shift-Enter. The formula will return X if A3:A11 is the same as C3:C11; otherwise...

Excel: Excel 2003, two different places, tenses

two different places, tenses, offence: Craig, I understand a little better but you didn t answer all my concerns. So the best I can do is present you with an IF statement which will occasionally fail to deliver the proper result. =IF(AND(Test1 50,Test2 50,ABS(Test1-Test2) =0.06*Average(Test1,test2),...

Excel: Excel 2003 Average on range if multiple conditions are met, seperate sheet, sumproduct

seperate sheet, sumproduct, summ: Collin, =sumproduct(--(All!$D$2:$D$776 = Summ by Task !$A6),--(All!$B$2:$B$776= Summ by Task !$A1),All!$H$2:$H$776)/sumproduct(--(All!$D$2:$D$776 = Summ by Task !$A6),--(All!$B$2:$B$776= Summ by Task !$A1)) should work entered the normal way. ...

Excel: Excel 2007, conditional formatting, sheet tab

conditional formatting, sheet tab, discrepancy: Eliot, You seem to have gotten most of it to work already, recognizing that Conditional Formatting is the way to go. What you ran into is that conditional formatting (CF) has limitations when working between sheets! So, you need to bring a reference...

Excel: Excel 2007, excel help file, significant digits

excel help file, significant digits, decimal places: Barry, My answer regarding Conditional Formatting will automatically format the number based on its magnitude. This answer is no-good if there is no correspondence between the magnitude of the number, and the number of places you want to see. However,...

Excel: Excel 2007 Drop Down List with images, conditional formatting, data validation

conditional formatting, data validation, text response: I like the idea, but I m not sure how easy it s going to be - data validation will allow the use of any font, so if you have one that has these entries then you could have the cell formatted as that font, and use conditional formatting to set the colour. ...

Excel: Excel 2007 Formula, column c, time expertise

column c, time expertise, hi bob: Russell, My name isn t Bob, so perhaps you sent it to the wrong person. Nonetheless if you put in this formula in any cell not in column C, then it will tell you the row number of the last cell in column C that contains a number: =MATCH(9.99999999999999E+307,C:C,1)...

Excel: Excel 2007: Pivot Table - Data Cube - WhatIf Analysis, pivot tables, excel 2007 pivot table

pivot tables, excel 2007 pivot table, intelligence development: Milton, I don t have enough information to help you with a complex problem like this. It is not working doesn t tell me anything about what s wrong. Please include a much more detailed explanation of exactly HOW it is not working and I will see...

Excel: Excel 97 with Vista os, free microsoft excel, spreadsheet program

free microsoft excel, spreadsheet program, question sign: If you burned those files to a CD they might be marked READ ONLY even if you copy them back to the hard drive. Right-click on each file and go to PROPERTIES. Then uncheck the READ ONLY box. That should allow you to edit them in Excel. Let me know if...

Excel: Excel to Access, Access to Excel, shift report, command button

shift report, command button, access database: It s certainly possible, and fairly easy, to write data to an access database - and indeed to pull data from access. I m not clear as to the structure of the access database (are you creating a new table to represent each sheet?) this code (which assumes...

Excel: Excel Character limit question, cell reference, character limit

cell reference, character limit, len: Naomi, You can use the =LEN(text-cell) function to return the number of characters in a cell. If you want to put in ... in the cell next to the text cell, use the IF statement =IF(LEN(text-cell) 30, ... , ) Obviously this equation has...

Excel: Excel Counting, excel help file, calculation spreadsheet

excel help file, calculation spreadsheet, sumproduct: Ryan, What Excel version are you using? if it is Excel 2007, then look at the COUNTIFS function, which can count all occurrences of records meeting multiple criteria. It is designed exactly for what you want. If you use Excel 2003, you will...

Excel: Excel - coding & matching cells, budget spreadsheet, vlookup function

budget spreadsheet, vlookup function, table of equivalents: It makes sense, and I can think of a few ways to do it - IF you are after just an overall total, then a series of SUMIF statements would do it - if you need to show the actual category, then you would need to build a table of equivalents and use a vlookup...

Excel: Excel countif formula, excel countif, column headings

excel countif, column headings, sumproduct: possibly a pivot table could be the easiest solution as this will allow you to put x,y,z as (say) rows and a,b,c as column headings - and sum them inbetween. SumProduct will also work - its =sumproduct(--(firsttest=whatever),--(secondtest=whatever)) ...

Excel: Excel Date cond formatting, st petersburg florida, last date

st petersburg florida, last date, florida usa: Mari You can use conditional formatting. Go to toolbar/format/conditional formatting In the pop up screen select cell value is, select less than The put in your formula =NOW()-365 Then pick your format then clic okay The formula =NOW()-365 takes...

Excel: Excel Date Question, expiry dates, many thanks

expiry dates, many thanks, spreadsheet: a few questions: 1 - when you say the date has more than 6 months to run you mean the date is more than 6 months in the future? Like 2/22/2010? 2 - You want the word EXPIRED in the same cell as the date or the cell to the right? Since you said youhave...

Excel: Excel- days overdue if not completed, cell a1, cell b1

cell a1, cell b1, column c: I apologize if there was a delay in answering your question. I was out of town for a week and I forgot to set myself on vacation - I just got back this morning. Lets assume your 8/21/09 is in cell A1 Lets assume TODAY()-A1 is in cell B1 The result...

Excel: Excel drop down list of formulas, excel drop down list, dropdown boxes

excel drop down list, dropdown boxes, ft row: Chris, Sure, look in Excel Help at the convert function to see what unit abbreviations it recognizes CONVERT(number,from_unit,to_unit) it lists what units it accepts. I would suggest two dropdown boxes. One for the first item and one for the second....

Excel: Excel Extra Rows and Columns, excel, print area

excel, print area: The way that I usually remove the extra spaces is selecting them, and then using delete row or delete column on the edit menu as appropriate. A normal delete of the data is usually not enough, somehow Excel remembers the area as being filled. Other...

Excel: Excel Formula, cell reference, sheet names

cell reference, sheet names, f18: Calfin, No, its not clear yet. How about if you were to say this? If cell F18 on sheet 1 is the total of a column, and the column is also on sheet 1 (for example F1:F17) and then I want the same equation that is in cell F18 on sheet 1 to be...

Excel: Excel Formula Question, basic principle, excel formula

basic principle, excel formula, holiday time: I m not sure I ve got enough information to go on here but I ll do my best hours worked is a simple function of end time minus start time - I would TEND to do this all in time, rather than converting times to decimal, but that s just me (and it depends...

Excel: Excel Formula for Timeclock, excel formula, quarter hours

excel formula, quarter hours, e7: Tim, You need a custom rounding function. There are several ways to do this, and here is one of them. Make a table that has the fractional minutes in descending order in one column, and the resulting quarter hour value in a second column. The...

Excel: Excel Function, excel help file, bin locations

excel help file, bin locations, excel function: Terry, I don t see how an IF statement can return a zero unless that s one of the clauses within the IF. Supposedly all you have are small , medium and large , and possibly unexpected . Or, maybe you have 0 at the end if all the nested conditional...

Excel: Excel formula, cell b1, dropdown arrow

cell b1, dropdown arrow, employee names: ON SHEET1: Say in cell B1 you have the column-header NAME (in bold). Starting in B2 and continuing down col B you have employee names. In C1 you have WORKING (in bold). Starting in C2 and continuing down col C, you have a Y or N for each employee, with...

Excel: Excel formula, excel formula, vlookup

excel formula, vlookup, mary joe: Chris I you were trying to find the name Bob, then I understand you to want this Mary Joe Bob Bob Phil Joe Mary Phil Phil Bob Bob Mary Bob Bob Joe then in Z1 would appear 3 if so, in B1 put in =if(A1= Bob ,A1,...

Excel: Excel formula problem, excel, if

excel, if, and: This formula is a bit long, but I chose it because it is easier to read and understand what it does: =IF(AND(ISBLANK(B1),ISBLANK(C1),NOT(ISBLANK(A1))), Accrual ,IF(AND(ISBLANK(A1),ISBLANK(C1),NOT(ISBLANK(B1))), Credit ,IF(AND(ISBLANK(A1),ISBLANK(B1),NOT(ISBLANK(C1))),...

Excel: Excel formula problem, array formula

array formula: Hi, You will need an array formula for this. The formula in cell F5 would be: =SUM(IF($B$3:$B$15=E5,IF($A$3:$A$15=$F$2,$C$3:$C$15,0),0)) but instead of using Enter, you will need to use Control+Shift+Enter. If done correctly, the formula will have...

Excel: Excel function, excel function, integers

excel function, integers, wikipedia: Dave, Yes there is a formula just like that. I found it on the Wikipedia. It is Sum of integers from 1 to N = (N*(N+1))/2 If your first number isn t 1, then you can find the sum from 1 to one-less-than-your first number...

Excel: Excel Help, conditional formatting, data validation

conditional formatting, data validation, popup message: jeanette, =sum(A:E) will sum up 5 columns or if you have specific rows =sum(A1:E2) as an example. You could apply conditional formatting to the cell with the formula to turn it red if it exceeds 100%, but there is nothing that supports...

Excel: Excel . Help, typical microsoft, sheet layout

typical microsoft, sheet layout, column numbers: The INDEX worksheet function would probably be the way to go with the definition you have given. This works as follows =index(YourDateTable,RowNumber,ColumnNumber) and will return the value from the given co-ordinates. The row and column numbers...

Excel: Excel Help please, stock sheet, vlookup

stock sheet, vlookup, sheet stock: Kerry, assume the sheet with the stock is names STOCK and the codes are in column A from A2:A200 assume each code has additional information about that code in columns B:M of the same row on sheet STOCK. in the materials list say in cell C5 put in...

Excel: Excel Macro?, excel macro, spreadsheet

excel macro, spreadsheet: use the control toolbar, insert a checkbox, right click it to view code and enter SOMETHING like this Private Sub CheckBox1_Click() If CheckBox1.Value = True Then Range( 11:14 ).RowHeight = 0 Else Range( 11:14 ).RowHeight = 12.75 End If End Sub ...

Excel: Excel Macro to Transpose, false selection, keyboard shortcut

false selection, keyboard shortcut, excel macro: Steven, Sub TransposeData() Dim rw_ans As Long Dim col_ans As Long rw_ans = Application.InputBox( Enter Number of Rows , Type:=1) col_ans = Application.InputBox( Enter Number of columns , Type:=1) With Worksheets( Transposed Data ) .Range( A1...

Excel: Excel Micro_output to another worksheet, excel function, date function

excel function, date function, yyyy: Hanxue, You can use the =DATE() function to create a DATE from the three parts, day,month & year. Read about this function in the Excel function reference to learn how to use it. Format the result as the type of date that you want to see....

Excel: Excel macro to add text to one cell based on another cell, partial string, rng

partial string, rng, excel macro: Sub test() Dim rng As Range Set rng = Range( a1 ) Do Until rng = If Right(rng, 3) = def Then rng.Offset(0, 1) = _ Almost & rng.Offset(0, 1) End If Set rng = rng.Offset(1) Loop...

Excel: Excel Non-sequential Comparison, master spreadsheet, sheet1

master spreadsheet, sheet1, two rows: Alan, I understand that all you want to do is flag the master sheet to show which rows in the master sheet match a row in the update sheet. I will assume the data in in Update.xls is in Sheet1. in a column in the master sheet, put in formula like...

Excel: Excel overtime formulas, excel timesheet, cell a2

excel timesheet, cell a2, cell a1: Ricci: I apologize if there was a delay in answering your question. I was out of town for a week and I forgot to set myself on vacation - I just got back this morning. Lets say the start time is entered into cell A1 as 08:00 AM . Lets say the end...

Excel: Excel Question, countif, fifth column

countif, fifth column, three pieces: Mike, This is slightly complicated and you ll have to do a lot of work to get a result. The first thing I d do is combine all four files into ONE workbook. This is easy. You ll have four sheets. Then in each of the four sheets, I d make...

Excel: Excel question, cell c2, a6 a8

cell c2, a6 a8, cell content: We want cell C2 to display the value in cell A2; C3 to display the value in A6, etc. Cell F2 should display the value in A4; F3 should display the value in A8, etc. This can be done with one formula, copied into all the cells. In C2, enter the formula:...

Excel: Excel Rate Calculator, rate calculator, financial functions

rate calculator, financial functions, case type: Lindsay, Unfortunately my expertise does not extend to financial analysis, (even a simple one like this), yet I can help you. Open the FORMULA ribbon (in 2007) or the Function Guide (in 2003) ( in either case, type an equal sign in a cell to begin),...

Excel: Excel rank correction factor, place finalist, ranked scores

place finalist, ranked scores, rank number: James, I agree... it is such a pain the way Excel chose to implement the RANK function. I also have struggled with this, and haven t found a good, easy way to correct this odd behavior. I can think of the beginnings of a way, though. Make a...

Excel: Excel Spread Sheet, usb barcode scanner, scanner interfaces

usb barcode scanner, scanner interfaces, excel spread sheet: Rommel, You ll need to research how to interface your barcode scanner to your computer to produce a data file, when you scan each barcode. Barcode scanner interfaces are not a part of Excel. Once you find the proper software for your barcode scanner,...

Excel: Excel - Sum Dollars of Two Met Criteria in an Array, cell d2, cell a1

cell d2, cell a1, consecutive rows: Jay What you need is the sumifand formula, unfortunatley this formula won t work in excel, So what I usually do is create one criteria out of the two by merging them with and & formula and then use the sumif formula. In your case assume that your column...

Excel: Excel sheet formulas to determine unduplicated records, d2 d2, excel sheet

d2 d2, excel sheet, pivot table: Adele, Here is one guess of what you want =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2)*($D$2:D2 calls )*($D$2:$D2=D2)) 1,0,1) If the test is any service but Calls is a match to any other service but calls then =IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2)*($D$2:D2...

Excel: Excel TIme help, conversion formula, cell a2

conversion formula, cell a2, richard florida: Tom Here is the formula that will convert the time formatted as HH:MM AM/PM to a decimal number. =(A2-INT(A2))*24 So if you have 7:30 AM in cell A2 the result of this formula will be 7.5 You can apply the same formula to the 2:00 PM cell. Assuming...

Excel: Excel and Time addtion, format cells, day zero

format cells, day zero, time values: I pasted your data into my worksheet and put in a sum formula and I got: 0:00 0:00 0:00 0:00 0:00 0:00 3:00 15:30 12:30 5:00 15:30 10:30 5:00 17:30 12:30 3:00 14:45 11:45 47:15 I did format the last cell as [h]:mm I would format...

Excel: Excel use as a searchable database on multiple criteria, medfile, physician name

medfile, physician name, cihost: Dani: Sorry for the late reply. EXCEL can do all of this and depending on much data you will need to store in the file will determine how fast or slow it will run now and eventually later. Without having seen the whole thing I would suggest not using...

Excel: Excel-VBA, excel ontime, chip pearson

excel ontime, chip pearson, excel vba: babu, Chip Pearson has a page that lays out how to do something on a recurring basis using VBA: http://www.cpearson.com/excel/ontime.aspx What you want to do (as I understand it) is have the sheet recalculate. You can put code in Chip s process like...

Excel: Excel VBA functions, vba functions, worksheet cells

vba functions, worksheet cells, excel vba: Function lastName(fullName As Range) As String Dim iChar As Integer, iLen As Integer lastName = iLen = Len(fullName) If iLen 3 Then Exit Function For iChar = iLen To 1 Step -1 If Mid(fullName, iChar, 1) = Then ...

Excel: Excel to Word, aidan, mail

aidan, mail, heritage: Go to word and choose the mail merge option - generate labels based on your data and drop the data fields in where they need to go - I m being VERY general here as you didn t specify version of Office being used and the methods are subtly different in each...

Excel: Excel Workbook and Sheets, sheet1, excel workbook

sheet1, excel workbook, month of the year: Amber, Assume you enter data in sheet1 in sheet2 in A1 put in the formula =Sheet1!A1 Now select Sheet2 A1 and drag fill/copy down, then select that data and drag fill/copy across. these formula will reflect whatever is entered in Sheet1 in the...

Excel: Excel Worksheet Macro - Revised, cell d2, cell c2

cell d2, cell c2, cell references: Stan, I don t have any way to respond to a rating - so best I can do is try to revise this response and hope you get it. r is column H so basecolumn is 8 loff = basecolumn - 4 = 8 - 4 = 4; so loff holds the value 4 (positive 4) r.offset(0,-4)...

Excel: Excell, free microsoft excel, question sign

free microsoft excel, question sign, 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: Excell Formulas, free microsoft excel, question sign

free microsoft excel, question sign, excel tips tricks: That s just a matter of simple math. I m assuming you re placing a 1 in each column representing the accept or non-accept values. If so, just count up each column uisng the SUM function, and then add both columns together to get the total number of responses....

Excel: Exel Counting Not to Exceed Question, free microsoft excel, excel tips tricks

free microsoft excel, excel tips tricks, looking for a new job: I would use another cell and say: =IF(A1 50,50,A1) Assuming your calculation is in A1, it will display either A1, or 50 if A1 is 50. You could enclose your current function in the IF statement, but that gets messy. You can use the IF function...

Excel: Expenses Between Two Dates, c description, column c

c description, column c, richard florida: Anthony Yes it is possible, here is how you do it. =SUMIF(A2:B100, 01/31/09 ,B2:B100)-SUMIF(A2:B100, 02/28/09 ,B2:B100) This formula is in two parts and sums the expenses between the two dates 1/31/09 and 2/28/09. The formula says--- 1--Sum...