About Experts Sitemap - Group 21 - Page 6 2016-09-20

Excel: Opening a workbook and edit sheet using multiple criteria, copy windows, wb1
copy windows, wb1, workbook set: Andy, Dim bk as workbook set bk = Workbooks.Open Filename:= D:AndyMyDocswb & _ textbox1.value & .xls Sheets( WK 01 ).Select Range( B3:B100 ).Select Selection.Copy Windows( wb1.xls ).Activate Range( A2 ).Select ActiveSheet.Paste you don...

Excel: occurance of character in formula, cell a1, format cells
cell a1, format cells, visual basic for applications: 1) The example you gave isn t a valid Excel formula. In Excel, you d use the formula: =1+2+3+4 2) Say you had a formula in cell A1, and you wanted another cell to display the number of + signs within the formula. This can only be accomplished using Visual...

Excel: How to open MS Excel 2007 data in MS Excel 2002?, free microsoft excel, google search
free microsoft excel, google search, excel tips tricks: There is a converter you can download from Microsoft s web site. I m sure you can find it with a Google search. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO...

Excel: To open a workbook in VBA, getopenfilename, dear tom
getopenfilename, dear tom, fname: Howard, Sounds like you want to display the file Open dialog: Dim fName as Variant fname = Application.GetOpenFilename() if fName = False then msgbox No file selected exit sub End if set bk = Workbooks.Open(fName) msgbox bk.FullName & has...

Excel: opening document, XLSX
XLSX: Dianne: XLSX is the extension of an Excel 2007 document. It is likely you have Excel 2003 or earlier and that is why you cannot open the file. Here are your options... 1) Contact the person who sent you the document and tell them to save it in an older...

Excel: Page Break View, www google co uk, page break
www google co uk, page break, google: you didn t give version of excel, but http://support.microsoft.com/kb/214691 http://support.microsoft.com/kb/201217 Both have an explanation of this and http://www.google.co.uk/search?hl=en&q=cannot+drag+page+break+preview+excel&meta= gives...

Excel: Pause And Continue If No Change, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, keypress event: How about a KeyPress event or a Button that changes a variable that your macro looks at to see whether or not to abort. 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: Pause And Continue If No Change, contiue, good answer
contiue, good answer, stock trading: Steve Sorry I won t be able to get to the question for a while, I am in the middle of a major project and should have taken my name off of the available list. I suggest you reask the question of another and I am sure you can get a good answer. Richar...

Excel: Pivot Table, PIVOT TABLE COLUMNS
PIVOT TABLE COLUMNS: Pravin: When you set up your pivot table be sure to drag the Expense Group to the rows section of the pivot table and the Aging Analysis to the columns section of the pivot table. Drag the balance owed to the values section of the pivot table. If...

Excel: Pivot Table, best regard, pivot table
best regard, pivot table, cell references: Khurram, You know what the steps are. You wrote them in your question to me! Just use the cell references inside the table instead of A1, B1, etc. A B C Debit Credit Balance 1 =A1-B1 2 ...

Excel: Pivot Tables, pivot tables, pivot table
pivot tables, pivot table, work areas: Difficult to answer based on the information I ve got to go on - a pivot table will display a summary of the data on which it s based, so if this is set to only be the current month (for example) then refreshing it will give the data ONLY for the current month....

Excel: Pivot tables in Excel, pivot tables in excel, how to create pivot tables
pivot tables in excel, how to create pivot tables, column sections: Abhi: That is a VERY general question. The quick answer is you highlight the table of information to be summarized (for example, A1:C100). It is important the first row contain the titles for each column. From there you click insert- pivot table or data-...

Excel: Please help if you can, vlookup, car names
vlookup, car names, car data: Steve, Yes, a VLOOKUP is the answer. The first step is to read the help file for this function. It will tell you ALL about it. You need to add a column next to your list of car names. This column will hold the top speed for each car....

Excel: Populating other cells based on a choice from a drop-down in Excel, drop down menus, home budget
drop down menus, home budget, column c: You need VBA to do this, and since you do, it s much easier to do it all by using a UserForm -- if you send me s sample file and repeat what you re trying to do, I can help -- send to bobumlas@yahoo.com, use subject of AllExpertsQ . If you don t want to...

Excel: Possible Combinations Help, int row, allexperts
int row, allexperts, performance issues: You are right, I cannot think on an easy way to tweak the formulas to allow exceptions. If possible, you may sort the ranges to place the data that you don t want to include at the end, and modify the ranges on the formulas to fit. Regarding those ranges,...

Excel: Possible with cell validation?, quality column, macro code
quality column, macro code, quality cell: So that I don t have to retype what has already been typed then try this link. http://www.contextures.com/xlDataVal02.html Without going to the time and effort to create an applicable relevant example of what you have to test this I think it can be...

Excel: PROTECT WORKSHEET CODE, window structure, inputbox
window structure, inputbox, true windows: Roger the protection you are talking about is workbook level protection. It has two options - window and Structure. You can select either or both. suggest you do apply the workbook level protection manually with the macro recorder turned on to...

Excel: Prepopulating a calendar in excel using a source table, calendar tab, source table
calendar tab, source table, alot: Carolynn I would need to know what version of Excel you are using, how the table is set up and how much information would be needed for each entry. If possible it would be much easier to work something out if I could look at the file. My email is richard(at)pcexperthelp(dot)co...

Excel: Pricing Spreadsheet, validation tool, data validation
validation tool, data validation, column c: Zuber, You have made this super-duper-extra complicated by your choice of data structure. Having three TYPE columns makes this otherwise-easy problem exceptionally difficult. Now you have to decide which column matches the TYPE you ve chosen so you...

Excel: Print certain ranges in excel, chip pearson, events application
chip pearson, events application, mark taylor: Mark, If you want the row visible except when you print it, you would need to use the BeforePrint event. This event is in the Thisworkbook module for the workbook For detailed information on Excel events see Chip Pearson s page: http://www.cpearson.com/excel/events.aspx...

Excel: Processing Text Data in Worksheet, matching number, worth 1000
matching number, worth 1000, allexperts: Since the number you are looking for is 2 for example and it is and always will be followed by a comma, then you can do something like: =IF(ISERROR(FIND( 2, ,A2,1)), , x ) which looks for 2, You can then keep the data the way it is, no need to...

Excel: Pull data from antoher spreadsheet, excel, indirect.ext
excel, indirect.ext: If you are using INDIRECT, you may use the non-Microsoft INDIRECT.EXT function, that will allow it to work on closed workbooks. You can find it at: http://xcell05.free.fr/morefunc/english/indirect.ext.htm It is a free add-on that you need to download and...

Excel: Punchlist on Excel, room designation, question pool
room designation, question pool, punchlist: Matt, You didn t ask this question of me, but I saw it unanswered in the question pool. It seems to me this task could be handled with a pivot table. A pivot table is normally used for summarizing numerical data such as costs for categories, but if...

Excel: Punchlist spreadsheet, room designation, jargon word
room designation, jargon word, data validation: Matt, The easiest way to do this is to use a FILTER, also called an AUTOFILTER in Excel 2003. However, before I proceed, I may have misunderstood your question since I do not know the jargon word punchlist , something evidently so familiar...

Excel: partial values in a field, excel, text
excel, text: You can use the MID formula for that, something like: =MID(A1,4,4) In this case, it will take the value of the cell A1 and it will return 4 characters starting on the fourth position. It is extracted as text, if you want to compare it with numbers you may...

Excel: pasting and linking highlighted cells into another sheet that sums values, endless problems, employee database
endless problems, employee database, unified database: Al, It sounds like what you really want to do is to create an employee database which will contain all the information about each employee s pay records and work schedules. There s no need to create an individual sheet for each worker.... this...

Excel: For pasting values, target cells, barcode scanner
target cells, barcode scanner, visual basic for applications: If you don t know Visual Basic for Applications (vba) coding, then automating processes like this in Excel isn t possible. If you do know vba, then use code like this, on the worksheet object in the Visual Basic Editor: Private Sub Worksheet_Change(ByVal...

Excel: pasting vlookup formula without breaking macro, mental glitch, autofilter
mental glitch, autofilter, paster: Den, My typo/mental glitch: Calculation is an attribute of the workbook (the setting is stored with the workbook), but it is set and read at the application.Level. (so in the object module, it is an attribute of the Application/Excel) so change Thisworkbook...

Excel: pause, pause statement, clock time
pause statement, clock time, calculation time: Hi Dave, There is a way to pause a macro, but only in increments of one second. It uses the application object s Wait method. For example, this code will pause the macro for 10 seconds: Application.Wait Now + TimeSerial(0, 0, 10) The argument of...

Excel: percentages, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, allexperts: If your data is in A1, you d type: =A1+(A1*0.10) 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: pivot table ?, summarising data, pivot table
summarising data, pivot table, desk top: Not really a pivot table, as that is a means of summarising data (so you could count up all the articles due in June for example). I would SUGGEST using Outlook to do this - simply set up calender or task entries for the items you want - this ASSUMES you...

Excel: pivot table row fileld labels, label cells, row labels
label cells, row labels, pivot table: Nate, To clarify, you want to fill in these labels in another area of the worksheet, where you plan to do the analysis... NOT in the pivot table itself... is that what you mean? If it is what you mean, why not just make a referential copy of...

Excel: price decrease, excel, percentage
excel, percentage: You could use a formula like: =A1*0.982 If you want to change the original values, you could type 0.982 on a cell, go to menu Edit- Copy, select all the values that you want to modify, go to menu Edit- Paste special, and on the Operation section select...

Excel: projects for kids, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, course outlines: Pick up an Excel book at the local bookstore and pick out examples. Take a look at my Tips & Tricks page for ideas: http://www.599cd.com/tips/excel?key=AllExperts Take a look at my Excel course outlines for ideas: http://www.599cd.com/excel?key=AllExperts...

Excel: Query reg: Ms Excel, loan amt, macro solutions
loan amt, macro solutions, question thanks: Raghu, By now you must have solved this! If not, read on! It COULD be done with equations,only, but they will be terribly complicated and difficult to explain. A far easier method would be to process the original sheet with a MACRO....

Excel: Question on Excel, hh mm ss, timer function
hh mm ss, timer function, value time: If by Sheet, you mean you open another workbook, then the problem would be that you unqualified code refers to the active workbook which probably doesn t have a sheet1. You need to qualify your code. Assume the name of the workbook containing sheet1...

Excel: Quick Question on deleting rows, original version, cells
original version, cells: try Sub Deleter() Set currentcell = Worksheets( Sheet1 ).Range( d5 ) Do While Not IsEmpty(currentcell) Set nextCell = currentcell.Offset(1, 0) If currentcell.Value=0 and currentcell.offset(0,1).value=0 and currentcell.offset(0,2).value=0 Then...

Excel: Random Selection with Greater Odds, blank cell, random function
blank cell, random function, odd occasion: John, Are you using Excel 2003, or 2007? 2007 has a new RANDBETWEEN function that would let you limit the random numbers to between 1 and 6. You d have to use one of the rounding functions (there are TEN!) to create an integer from the random number....

Excel: IF function?, tie breakers, column c
tie breakers, column c, 2 steps: Paul: Your formula does the following: If cell D10 is blank then the formula returns an empty cell ( ). Now, the formula is written perfectly fine and it should be working; however, it will fail if Excel doesn t believe cell D10 is actually blank. ...

Excel: Ranking a number vs. a pivot table, index sheet, range index
index sheet, range index, pivot table: INDEX( Sheet 1 !$15:$200,0,MATCH(1E+307, Sheet 1 !$16:$16,1))) should give an invalid reference. the 0 parameter is not valid. So are you saying that if the last column of the PT were J, you d want to take the values in J3:J12 (or whatever without the last...

Excel: Rate of increase, cell b2, percentage increase
cell b2, percentage increase, percentage rate: Rex I would approach it simply First in a separate formula calculate the percentage rate of increase. Then calculate the average of those percentage rate increases. Assuming the numbers are in cells A1 to A5 The first formula would be in cell...

Excel: #REF! Error, pivot table, approach works
pivot table, approach works, tim tim: Tim, I would think that your attempt to use IF with an embedded ISERROR would be successful. I tried an experiment and ISERROR does indeed detect the Dreaded REF Error (DRE). But perhaps the problem lies in the way your DRE is generated, not really...

Excel: Having Realtime in excel without having to select F9, support folks, hermanson
support folks, hermanson, questioner: Allan, Thanks for the 10! This is from the FAQ for experts on the site. ================================================================== How do I dispute a rating I received? We will only change a rating if we receive an email from the questioner...

Excel: Recursive formula checking values, excel, lookup
excel, lookup: With that layout, you could use the following array formula on cell D2: =OFFSET($B$1,MAX(($A$2:$A$1000=$C2)*ROW($A$2:$A$1000))-1,0) Enter it with Ctrl+shift+Enter, and it will return the value on column B corresponding to the last entry matching the value...

Excel: Reference Cell, sheet1, equivalent value
sheet1, equivalent value, aidan: The picture doesn t make it very clear, but it s possibly INDIRECT that you are looking for =indirect( Sheet1!A & Sheet2!b3) with a 3 in sheet2 B3 would translate to =Sheet1!A3 and return the equivalent value Sorry if this was NOT what you...

Excel: Reference formula only from another sheet, ncaa basketball tournament, team bracket
ncaa basketball tournament, team bracket, excel formula: Nick, I am still confused by your explanation. You seem to have a fundamental misconception of what an Excel formula can do and what it can t do. So to help you out, I ll offer this: A formula exists in a cell. A cell can only hold ONE value....

Excel: References to ([LIST LAST ROW]+1), error type, dependants
error type, dependants, asterisk: Greg, It seems like you have already explored all the options, so I don t have much more to add. My advice is to upgrade to 2007, for, as you observed, this effect does not happen there. I played around with a sample table and also couldn t get the...

Excel: Referencing External Sheet Based On Text, excel, indirect.ext
excel, indirect.ext: This is possible with an Excel extension, that includes the INDIRECT.EXT formula. Check the add on and the help on this function at: http://xcell05.free.fr/morefunc/english/indirect.ext.htm It will allow you to craft text strings that reference values on...

Excel: Reg Sum Function, positive reply, sumproduct
positive reply, sumproduct, sum function: Girish, This produces 6.29 with those numbers in A1:A4 =SUMPRODUCT(--(INT(A2:A4)))+INT(SUMPRODUCT(100*(A2:A4-INT(A2:A4)))/40)+((SUMPRODUCT(100*(A2:A4-INT(A2:A4)))/40-INT(SUMPRODUCT(100*(A2:A4-INT(A2:A4)))/40))*40)/100 -- Regards, Tom Ogilvy ...

Excel: Reg Sum Function, positive reply, entering time
positive reply, entering time, sum function: GIRISH Here is the problem When you enter 1.12 excel thinks you are entering 1 plus 12/100 of 1. this could be 1 plus 12/100 of an hour, but it isn t 1 hour and 12 minutes. If you make your entries like this, 1:12, excel will know that you are entering...

Excel: Remove text in paranthese from cell, sdsi, collumn
sdsi, collumn, character position: Stephen, You re on the right track using SEARCH() and LEFT(). Add RIGHT() and LEN() to the mix and you ll have it! SEARCH() for ( as you did. Search returns character position N, where the ( is found. Take the LEFT() characters from 1 to...

Excel: Removing, return character, weird one
return character, weird one, problem thanks: Linda, So lets find out what these characters are. Then we can replace them with spaces. If spaces get put on the end, we can remove them with TRIM. Hopefully these characters are all the same. You ll have to do a little bit of experimenting....

Excel: Removing e-mail hyperlinks in Excel, excel expert, insert hyperlink
excel expert, insert hyperlink, proper terminology: From 2007? No wonder I don t remember. What I suggested will not work when multiple cells have been selected. Unfortunately, this command is available only when you have selected a single cell. To quickly deactivate all hyperlinks, you need to use a macro....

Excel: Removing multiple items from stock with excel, stock sheet, user interface
stock sheet, user interface, computer components: Mitchell, Try something like this: Sub UserInterfaceRemoveFromStockLoop() ////////////////////||||||||||||||||||||||||||| ************************Remove From Stock************************** |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||...

Excel: Retrieve data from multiple critierias, product id numbers, product id number
product id numbers, product id number, container size: Diav, Assume the table is in A2:D100 assume you put the container size in F1 product code in G1 country in H1 in J1 =IF(AND(F1 ,G1 ,H1 ),INDEX(D:D,SUMPRODUCT(--(A1:A100=F1),--(B1:B100=G1),--(C1:C100=H1),ROW(A1:A100)),1), ) -- ...

Excel: Returning multiple rows with same value, cell b10, example spreadsheet
cell b10, example spreadsheet, array formula: Bryan, Assuming you entered the formula correctly, that is indicative of the fact that you didn t array enter the formula. From the article - one of several such references enter the following array formula (CTRL+SHIFT+ENTER) in cell B10 to determine...

Excel: Round off any value with condition in excel 2003., number int, number 0
number int, number 0, frac: Nabam, This can be done quite easily, but first I need to be sure I understand the rules. Giving rules by example only, as you did, is not always enough. So I have to guess what your rules are from your examples. If I have guessed wrongly, then my...

Excel: Rows to Columns, pivot table, mail system
pivot table, mail system, style color: POSSIBLY the way to go would be data menu, pivot table and chart report - this would allow you to group the data, though not necessarily in the way you want. Otherwise, it would PROBABLY have to some form of array filter I think - which is fairly complex...

Excel: Run macro in one workbook on a different workbook, repetitive tasks, ogilvy
repetitive tasks, ogilvy, macros: Roger, Yes, thisworkbook refers to the workbook containing the macro. you need to change ThisWorkbook to ActiveWorkbook Sub ALLOW() Dim Sh As Worksheet For Each Sh In ActiveWorkbook.Worksheets Sh.Unprotect PASSWORD Next Sh End Sub of course...

Excel: How to read excel data and insert into sql db, confidential staff, rowcount
confidential staff, rowcount, staff database: Ok, well again a real world case - this doesn t loop through the sheets but something like dim sh for each sh in activeworkbook.worksheets do the loop, referencing the worksheet by sh next would give you the loop Sub ExportToSchemesDataBase()...

Excel: real time macro, range b23, decimal fraction
range b23, decimal fraction, time macro: I really would not recommend converting time to a FORMATed value, as that returns a string - so 9 is MORE than 11 as 1 comes before 9 alphabetically. My suggestion was to use the TIME itself - so msgbox now()-int(now()) would display a decimal fraction...

Excel: resize cells command, ogilvy, cells
ogilvy, cells: dave, if this is a vba question, I interpret this as you select a cell and you want to expand the selection to the area of contiguously filled cells around it activecell.CurrentRegion.Select if this is a non vba question ctrl+Shift+8 (ctrl+*) ...

Excel: rewording, additional info., geologic structure, geologic structures
geologic structure, geologic structures, region6: Kade, Let s say you put your formula in row 2 and it summed up what you wanted. You should then be able to copy that cell, go down 17 cells and paste it and it should get you the second sum you want. So Excel should adjust the formula relative to...

Excel: running Macro via a shortcut, code execution, screen updates
code execution, screen updates, shortcut key: Well, it seemed to work fine for me. Does this version of the macro do any better? Sub ScrollAllSheetsTopLeft() Dimension variables Dim sht As Worksheet Dim shtActiveSht As Worksheet Determine the activesheet at start of code execution...

Excel: Save As problem, worksheet menu bar, false application
worksheet menu bar, false application, true application: Jim, In the same module (thisworkbook module) Private Sub Workbook_BeforeClose(Cancel As Boolean) Application. CommandBars( Worksheet Menu Bar ) _ .Controls( File ).Controls( Save As... ).Enabled = True Application.CommandBars( Worksheet Menu...

Excel: Search for cell colour and return results in MsgBox, conditional formatting, red cells
conditional formatting, red cells, sheet names: Lee ========= Previous Post ===================== I am using Excel 2003 on WinXP SP2 and hoping you can help as I am very rusty on the little Excel/VBA knowledge I have. I have created a workbook used for tracking contacts with clients. There is one sheet...

Excel: Search using contents of a text box., excel vb, search routine
excel vb, search routine, column c: Hi Stewart, You could use code like this in the click event of your button (we re looking in column C of sheet1 here): Dim sStringToFind As String sStringToFind = Textbox1.Text With Worksheets( Sheet1 ) .Range( C:C ).Find(What:=...

Excel: Searching a column and adding row information to a new sheet, sh1, rng
sh1, rng, ogilvy: PJ, sure, I didn t give you an executable program. I gave you a code snippet that might do what you want but there wasn t enough information to do everything (such as determine which app to look for). I have no idea about anything that you have not told...

Excel: Searching in Excel, word doesn, single white female
word doesn, single white female, cells: Jeff, Its not only possible, its easy! For each word you want to search for, create a column of SEARCH equations. Refer to the word-to-search-for as a mixed reference to a cell at the top of the column ( as in B$1), and make the reference to the...

Excel: Searching in Excel, search worksheet, single white female
search worksheet, single white female, filter auto: Yes, and as always, lots of different ways - if the values are in different columns, then data, filter, auto filter and select your choices - OR use the SEARCH worksheet function with an OR and AND statement - eg with data in A2 =AND(ISERROR(SEARCH( single...

Excel: Separating Glucose Readings, glucose readings, lunch dinner
glucose readings, lunch dinner, time range: I follow the question, but not sure on the detail - is the data in a file that could be linked to, is it formatted in a way that excel can cope with? Having said all of that (which relates to GETTING the data into excel - and I m only mentioning it as if...

Excel: Show only Rows that contain any of certain values listed...., example id, hth
example id, hth, a1: Assuming your data starts in A2 (A1 being a header), enter this formula in G2 (assuming col G is unused-- the important thing is not to put it in row 1): =OR(A2=710,A2=1504,A2=332) Then select your data, use Data/Filter/Advanced filter, and enter $G$1:$G$2...

Excel: Sick Leave Accrual, sick leave accrual, third test
sick leave accrual, third test, n3: Karen, I have a couple of ideas for you. Would it be accurate enough to take the number of days between the end of the year and the hire date, divide by 30.417 (avg no of days per month over the entire year), take the integer part of the result,...

Excel: Simple formula, example spreadsheet, quantity column
example spreadsheet, quantity column, database function: Jade, There are many ways to do this. But none of them will allow you to destroy or delete the source rows containing the original information. However, you can either move or copy the results to another sheet. 1) Use the SUBTOTAL function....

Excel: Sorting Multiple Rows In Excel, trying sort, lottery numbers
trying sort, lottery numbers, colum: Neil, It all centers on use of the countif worksheet function. If you want to send me a sample worksheet so I can see how the numbers are laid out (you can put in dummy numbers), and tell me what you want to see, I can lay it out for you. twogilvy@msn.com...

Excel: Sorting multiple rows in Excel 2007, sort operation, set of numbers
sort operation, set of numbers, second choice: Mahmoud, That is the way Excel is designed. There is no option to select mulitple rows and have each row sorted independently of the others left to right. This is the same as the normal way to sort when you sort vertically. You should only select...

Excel: Sorting Options, alphabetical order, stacy
alphabetical order, stacy: Stacy I am assuming that the names are in one cell, the quickest option is to split the column and then sort the table on the 2nd column which will have the names. If you can tell me the version of Excel you are using I will send instructions on what to...

Excel: IF Statement, colume, automaticly
colume, automaticly, ogilvy: Arlie Assume you make the Y or N in A1, then in B1 =if(A1= , ,if(or(A1= Y ,A1= N ),1196000+row(), )) if the first entry is in A9 as an example, then you would just adjust the formula in B9 =IF(A9= , ,IF(OR(A9= Y ,A9= N ),1196000+ROW()-8,...

Excel: IF Statement, 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: Students and Remaining Balance, cell a2, budget calculator
cell a2, budget calculator, cell a1: Issa: Before I write another formula to provide what you need I feel I should ask for clarification: What if there are 16 students? Is this now 2 groups since you have 2 sets of 8 students or do you want this to show as 1 GROUP + 6 REMAINING? In...

Excel: SUMIF with defined names, sumif function, sumif formula
sumif function, sumif formula, seperate sheet: You should be able to use range names in the sumif formula so I don t know why the formula is not working right and without being able to see the file then it is hard for me to troubleshoot. And unfortunately for you I don t accept emails with attachments....

Excel: SUMIFS, nathan daniel, sumproduct
nathan daniel, sumproduct, daniel daniel: Daniel: Lets assume column A has names like this: Nathan, Daniel, Daniel, Daniel Lets assume column B has dates in it like this: 1/1/09, 1/11/09, 1/14/09, 1/25/09 Finally, lets assume column C has amounts in it: 11, 12, 14, 56 Now lets assume...

Excel: Sum formula, n l1, sum formula
n l1, sum formula, sumproduct: If your data starts in D1 ( Date ), then if your test data is in L1:N1 (date, svc, s/no), this formula will add the times where it matches (expand the cells beyond row 100 if necessary) =SUMPRODUCT(N(L1=D$2:D$100),N(M1=H$2:$H100),N(N1=I$2:I$100),K$2:K$100...

Excel: Sumif, pivot table, hardeep
pivot table, hardeep, noida: The EASIEST way to do this would be via a pivot table, as this would automatically group things together and allow you to do an Average as well as a total. If for any reason a pivot table is not acceptable, then you COULD use a SUMPRODUCT function as follows...

Excel: Summing, skipping rows, sheets, arizona department of water resources, hydrologist
arizona department of water resources, hydrologist, timespan: Kade, I usually create blocks of references to discontiguous cells using the ADDRESS function inside an INDIRECT function. Before I proceed, I hope when you say sum blocks of between three and nine cells , that there isn t some variation in...

Excel: Summing, skipping rows, sheets, geologic structure, geologic structures
geologic structure, geologic structures, region6: Something along these lines would seem to do it for you =SUM(INDIRECT( A &MAX(1,(ROW()-1)*20)& : & A &MAX(3,((ROW()-1)*20)+3))) the INDIRECT worksheet function converts a text entry into a cell/range reference, so you can use (relatively) simple maths...

Excel: save the work sheet as individual file, current sheet, work sheets
current sheet, work sheets, formatting: (1) save as a text format file - these only support single sheets, so only the current sheet would be saved, but you d loose formatting and formulas (2) right click each sheet and move or copy to another workbook - save the resulting file and repeat until...

Excel: search data on multiple worksheets (looping), excel macros, data consolidation
excel macros, data consolidation, sum formula: Sub SearchForStrin() Dim LSearchRow As Integer Dim LCopyToRow As Integer For each sh in Array( 01 , 02 , 03 , 04 , 05 , 06 , 07 ) Sheets(sh).Select Start search in row 1 LSearchRow = 1 Start copying data to row 2 in Sheet2...

Excel: searching in vba, expired contracts, vba macro
expired contracts, vba macro, time jason: Jason, If your heart is set on this being a VBA macro, you ll have to ask a different expert. I don t advise on macros on this site. However, this seems like it would be really easy to do with a FILTER. Have you tried applying a FILTER to your...

Excel: sending a macro to someone else, excel, vba
excel, vba, import: You can save the macro as a file with extension .bas and send that file to him. To do so, open the Visual Basic editor, and on the project explorer right click on the name of the module that contains the macro. On the emergent menu you will have an option...

Excel: separate text in cell, excel, text to columns
excel, text to columns: In this case you can probably use the menu Data- Text to columns. Select the range of data with the information, start the Text to columns wizard, and select comma as the separator. On the third and last screen you can either overwrite the values, or place...

Excel: shared work book, track changes, multiple users
track changes, multiple users, corruption: Hi Nathalie, There is an option to track changes, do you have that turned on? (you ll have to unshare the workbook first I think). But. I strongly advise against using shared workbooks. I read lots of problem reports on this feature. Most of them...

Excel: show products as blank not #VALUE!, conditional formatting, format button
conditional formatting, format button, relative address: Paul, If you don t want to modify your formulas, then you could use conditional formatting to change the font to white (assuming a white background) if the cell contains an error value select the whole data range/table. Assume the activecell in the...

Excel: simple question on efficiencies, simple math formulas, pivot table
simple math formulas, pivot table, processing power: Nicolas, My feeling is that a pivot table would be much more efficient. The pivot table algorithm is built into Excel so Excel intrinsically knows how to do it . All a pivot table has to refer to is the data table. With equations, Excel has to read...

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

Excel: How do I sort this data?, highest score, ascending order
highest score, ascending order, aidan: I m confused, in that excel should be quite happy to sort the results of formulas into ascending order, ALTHOUGH I would normally expect to sort the entire sheet, so if you are sorting only a portion of it the formulas COULD get messed up. Otherwise, you...

Excel: sorting 2nd string, genevie, paul thanks
genevie, paul thanks, worksheet functions: genevie, You could write your own macro to do it. if you want to use the built in capabilities of Excel, you could copy your data to the next available column on the right, Then select the copied data and do Data= Text to columns. In the resulting...

Excel: spreadsheet - 2 items, continuous block, blank row
continuous block, blank row, header row: To ensure the sort works correctly either select NOTHING before doing the sort, OR select the entire data range - excel will then treat (in the first instance) the data from the header row to the first blank column and the first blank row as a continuous block...

Excel: If statement with a count, sumproduct, pivot table
sumproduct, pivot table, neat trick: The QUICKEST way to do a bulk analysis of this sort of data is to use a pivot table - data menu, pivot table and chart report (different in office 2007) - you would then drop the TYPE in the left hand area, the region in the top area, and either of these entries...

Excel: IF statement help, IF
IF: Hi David, Give this formula a try: =IF(H15 2000,H15*1.12,IF(H15 2500,H15*1.1,IF(H15 3000,H15*1.09,IF(H15 3500,H15*1.08,IF(H15 4000,H15*1.07,0)))))*1.25 All of your conditions multiply by $1.25 so that gets applied to the entire result. I leveraged...

Excel: If then...string, excel, vlookup
excel, vlookup: Yes, it is possible, but in this case my recommendation would be to use VLOOKUP instead of IF. To use VLOOKUP, craft a table with the different items that can be on the drop down and the price on a separate range (for example range A1:B20 on sheet2), and...

Excel: sub-tables in excel, cell b1, computer novices
cell b1, computer novices, left hand corner: Danny: How about this sample file? http://home.swbell.net/nate-sus/excel/fordanny.xls Note #1: The ultimate formula searches by capital letters. That is what I have made sure to use lower case in the word item . Assuming you will use something...

Excel: sum daily data by week, blank rows, sum sum
blank rows, sum sum, increments: Robyn, If your data is PERFECTLY REGULAR with absolutely NO skipped days, than you could build a simple equation which would sum the next seven days and copy it down. Write a formula next to one of the rows of data. It will be a simple SUM(H7:H13)....

Excel: sum data range, time interval, strange version
time interval, strange version, cell references: Stefano, I ve given you several weeks to solve your problem. From the wealth of information and ideas I gave you, you should have been able to extend my solutions to cover your data setup. Though I didn t provide the exact equations you would use,...

Excel: summing across columns using a criteria, array formulas, a1
array formulas, a1, aspx: David You can sum across columns using the Ctrl + Shift + Enter which will make the formuala an array. Have a look at this link on Array formulas http://www.cpearson.com/excel/ArrayFormulas.aspx If you are still having a problems send a copy of the...

Excel: summing figures in a colum depending a a set of conditons in two othes, question column, david answer
question column, david answer, minus signs: David, =SUMPRODUCT(($A$7:$A$239=12),($H$7:$H$239= general ),$AH$7:$AH$239) That formula is not what I suggested. the double minus signs in front of each condition was a critical part of what I suggested. Here would be your formula modified to work...

Excel: suning a cell, array formula, sum function
array formula, sum function, cell a1: Hi Ray, I devised this highly complex array formula that seems to do the job: This formula works on the number in cell A1. No need to adjust the reference in side the INDIRECT function: A1:A can be left unchanged. =SUM((INT(A1/10^(ROW(INDIRECT(...

Excel: Table to Text to Time, hh mm ss, duration notes
hh mm ss, duration notes, time duration: You can use paste special again to solve it. In this case, type the number 60 on a cell, copy it, select the range with the data that you want to modify from hh:mm to mm:ss, go to menu Edit- Paste special and select Divide on the Operation section. It will...

Excel: Tally in Excel, life signs, incorrect choice
life signs, incorrect choice, language world: Is this a count of the entries in a series of cells, or do you want to keep a running total of the number of times one cell is changed to a given value? The second requires VBA and is a little dangerous in that an incorrect choice would be counted even if...

Excel: Testing on dates in Excel, cell a1, time today
cell a1, time today, 3 years: Never only one way to do it - I d probably use EDATE if(a1 EDATE(TODAY(),-36), Blue , Green ) but you could also do =if(a1 DATEVALUE( 01/01/ & year(now())-3), Blue , Green ) (I m lazy so use NOW rather than TODAY - now returns date and time,...

Excel: Time Calculation, time calculation, lunch time
time calculation, lunch time, currency: Hours are stored as parts of a day. 1 hour = 1/24 of a day, formatted as a time. If you enter =1/24 in a cell, format it as h:mm AM/PM, you ll see 1:00 AM. So, if you have hours and a rate, you need to multiply them together and again by 24, and probably...

Excel: Tranpose Formulas, correct formulas, cell references
correct formulas, cell references, d2: Hi Vincent, Unfortunately, I don t have any great answers for you. The transpose option is only good for constant values and absolute cell references. In order to properly transpose the references, your formulas need to be = working out !$D$2, etc. This...

Excel: Transfer formula from another sheet, excel, multiple sheets
excel, multiple sheets: This works on Excel 2003, so it will probably work on 2007. Select all the worksheets where you want to have the formula (either with Shift or Ctrl on the tab with the name), and type the formula on cell C4 as you would do normally. When you go to the other...

Excel: Transfer One Worksheet's Data Across Multiple Worksheets, Sequentially, indirect address, sheet names
indirect address, sheet names, address portion: Bryan, Its an interesting idea to try to make a formula from a text string! You can certainly do such a thing, EXCEPT that you will end up with a text string, not a formula! You d have to copy and paste-special-values on the resulting text string,...

Excel: Trouble Including a Go To Special Refrence in a Charting Macro, mychart, sheet updates
mychart, sheet updates, check boxes: Set myChart=ActiveSheet.ChartObjects( Chart 7 ) isn t correct, because according to your followup, Chart 7 ISN T on the activesheet (Graph Sheet), but rather on the sheet Graph. Use this code: Sub changeChartSource() Dim myChart As ChartObject ...

Excel: True or False, question statement, a1
question statement, a1, cells: Duncan, All you have to do is write any logical equation into the cell(s). If you are comparing cells against the value 10, put 10 in a cell, like A1. Then your logical equation will be something like =J5=$A$1. Assuming J5 contains one of the...

Excel: Have the tab name pull from a cell, thier names, b14
thier names, b14, aidan: Not sure I follow - it sounds as though you want excel to rename a random worksheet with whatever you type in B14? That is PROBABLY possible, but I don t THINK it s what you meant (if it is, it sounds like a one off job to create all necessary sheets - which...

Excel: If with text, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, 599cd: Use the MATCH function to find out if a value appears in another 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 VIDEO TUTORIAL. Go to http://www.599cd.com/Excel/AllExperts...

Excel: toggle button?, valuation model, checkbox
valuation model, checkbox, cool things: I don t know what you mean re the projects or on or out -- what does that actually mean? Protect the workbook? change formulas? In any case, it sounds like instead of a button, you can simply use a checkbox which is clearly on or off (checked or unchecked)....

Excel: tough excel question, excel, time formats
excel, time formats, microsoft excel: Phillip, The first important thing to consider is what format the date/times are in. My answer is assuming the format is 3/1/2008 10:00:00 AM . You can make it this format by choosing format cells. your formula in column e11 should be: =IF(((d11-c11)*24)...

Excel: transpose question, detail detail, blank cell
detail detail, blank cell, ogilvy: David, I would use a macro such as this one: Sub ChangeData() Dim r As Range, ar As Range, r1 As Range Set r = Columns(1).SpecialCells(xlConstants) For Each ar In r.Areas ar.Copy ar(1).Offset(0, 1).PasteSpecial Transpose:=True Next Set r1...

Excel: Update, individual department, master price
individual department, master price: for each individual sheet, for any item on the master list enter it s value as a linked cell - i.e. a formula like this =[Workbook name and path]Worksheetname!CellRef when the individual sheet is opened, it will prompt to update the values, and will...

Excel: User Form wont loop correctly, word data, searchin
word data, searchin, range c: Stewart, You need to change the code that goes to the last location to just go to the first location. Stuart Resnik s code for the next button assumes that you are working from top to bottom. there is nothing in your code that would cause the userform to...

Excel: Using ActiveCell as value in Range, gap down, windows document
gap down, windows document, contiguous data: Actually, CS is column 97 from the immediate window: ? Range( CS1 ).column 97 Anyway, you can adapt the below to reflect your actual situation Sub ABC() Dim sh As Worksheet, sh1 As Worksheet Dim i As Long, r As Range, cell As Range Set sh =...

Excel: Using Excel to Match People to Classes, open subjects, logic problem
open subjects, logic problem, lookup functions: Ally, This is a massive logic problem, not easy to solve by any simple set of equations. I doubt that high schools use Excel for this type of task... this is a scheduling task that is best solved by repetitive application of a scheduling algorithm....

Excel: Using functions on selected data in Column, statistics formulas, column name
statistics formulas, column name, ogilvy: Rodger, first, you don t usually don t need to array enter sumproduct - not always the case, but should be here: Average: =SUMPRODUCT(Score,--(Date =Date_Beg),--(Date =Date_End))/SUMPRODUCT(--(Date =Date_Beg),--(Date =Date_End)) the above is fine...

Excel: Using INDEX with multiple criteria, g222, committee names
g222, committee names, table cells: Beth, If I understand the situation correctly, then this should do that: =IF(SUMPRODUCT(--(Table2!$G$2:$G$222=$A2),--(Table2!$H$2:$H$222=B$1))=0, ,INDEX(Table2!$B$1:$B$222,SUMPRODUCT(--(Table2!$G$2:$G$222=$A2),--(Table2!$H$2:$H$222=B$1),ROW($B$2:$B$222)),1))...

Excel: Using a Macros, blank copy, shold
blank copy, shold, question thanks: Tony, You said you had a macro that The sheet has protected cells so in the macros I take protection off to copy all cells and then put protection back on all sheets. You would need to integrate the code snippetapproach I suggested within your...

Excel: Using nested IF function combined with AND, OR, NOT, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, isnumber: I can t write the whole thing out for you, no. That s for you to learn. You need nested IF functions and possibly the ISBLANK, ISNUMBER, AND and OR functions. Here s some help: You can use the IF function to have Excel place a value in a cell based on...

Excel: Using the value in a range as the title for a command button, exit design, commandbutton
exit design, commandbutton, button title: From your main Excel menu, choose View, Toolbars, Control Toolbox. Use the Control Toolbox to enter your CommandButton on the sheet. Right-click on the button and choose Properties. In the Properties box, it will list a default name for your button, like CommandButton1....

Excel: urgent help, timb, formulae
timb, formulae, aj: Say your original text is in col A, e.g. with A1 being: a. k. timb and A2 being: aj patel In cell B1, enter the formula: =SUBSTITUTE(A1, , ) then copy it down col B. It will take each entry in col A and display it with all spaces removed...

Excel: userform textbox follow-up, paste words, xls files
paste words, xls files, brain storm: Attis, I believe I already provided a revised answer (according to the archives - 2/04/2009) to this question which incorporated the find command in response to your first followup. The answer may have started out with The Expert Can t Answer Your Question...

Excel: using a sort macro with multiple sheets, hello bill, sort command
hello bill, sort command, macros: change ActiveWorkbook.Worksheets( Sheet 1 ) to ActiveWorkbook.ActiverSheet - see below Sub SORT() SORT Macro Macro to sort column A small to large ActiveWorkbook.Activesheet.AutoFilter.SORT.SortFields. _ Clear ActiveWorkbook.Activesheet.AutoFilter.SORT.SortFields.Add...

Excel: using a vlookup text result in an IF statement, vlookup formula, logical test
vlookup formula, logical test, text result: Philip, First of all, you are using an overly complicated IF statement. =IF(D1=F1,TRUE,FALSE) can be replaced by =D1=F1 It may look funny, but this is a logical test whose answer can only be TRUE or FALSE. You don t need...

Excel: using a vlookup text result in an IF statement, vlookup formula, case conversion
vlookup formula, case conversion, text result: Difficult to follow - the formula for testing is more complicated than it needs to be =D1=F1 would give a true/false result without any tests. POSSIBLY you need a TRIM statement (leading/trailing spaces) or possibly a case conversion statement? Can...

Excel: #VALUE error, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, blank cell: You can use the NZ or ISBLANK functions. 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 Cordially,...

Excel: Validation for duplicate records, excel, countif
excel, countif: In this case my recommendation would be to use conditional formatting for the task column, so it will be highlighted if the user enters a duplicate value. To do so, select the full column A (you can limit it to the rows you want, but this way it will always...

Excel: Validation - EXCEL, dependant, xls
dependant, xls, validation: Sorry, the question isn t clear - one cell can only contain one set of choices - are you wanting to make the choices in one cell dependant on the choice in another cell? if so, see http://www.aidanheritage.byethost3.com/excel/example%20choices.xls which...

Excel: VB Code, ogilvy, spreadsheet
ogilvy, spreadsheet, headings: Roger, these worked for me: Private Sub CommandButton1_click() remove filter me.AutoFilterMode = False End Sub Private Sub CommandButton2_click() add filter Dim r As Range Set r = me.Range( B2 ).CurrentRegion If r.Row = 1 Then Set r...

Excel: VB Macro, sname, rng
sname, rng, excel sheet: Rolf, Sub Datain() Dim sName As String Dim rng As Range, col As Long Dim rw As Long, l As String sName = Application.GetOpenFilename( _ FileFilter:= Text Files (*.Bls),*.Bls ) If sName = False Then Exit Sub Set rng = Range( A30 col = 0 rw...

Excel: In VB, selecting the first blank cell in a column, excel, vba
excel, vba: You can combine the COUNT function from Excel with the Offset from VBA, with something like: range( A1 ).Offset(application.WorksheetFunction.Count(range( A1 ).EntireColumn),0).Select If the range of data is continuous, this will select the first blank cell...

Excel: VBA copy values only, vba code, m75
vba code, m75, sick days: Let s say that what you want to do is copy what s in the range defined by: SickDay.Offset(-1, -1) and paste-special its VALUES to the range defined by NewSh.Range( E75:M75 ).Cells(0 & Rcount) The vba code is: SickDay.Offset(-1, -1).Copy NewSh.Range(...

Excel: VBA excel help, max match, max line
max match, max line, string str1: 1 - I don t know what matches is -- it s not native VBA code. 2 - I don t see matching in your example, so please send me the wb which contains the data and the VBA code so I can see what you mean. send to bobumlas@yahoo.com, use subject of AllExpertsQ...

Excel: VBA-help required, excel programming, problem thanks
excel programming, problem thanks, checkbox: Hi Rajat, You could do it like this: Sub EnableDisableBoxes() Dim oObj As OLEObject For Each oObj In ActiveSheet.OLEObjects If TypeName(oObj.Object) = CheckBox Then oObj.Object.Enabled = True End If Next...

Excel: VBA procedure, c cells, rw 1
c cells, rw 1, column c: jw I will assume you know how to use macros since you are asking for one. Sub ABC() Dim cell As Range, c As Range Dim i As Range, rw As Long Dim sh As Worksheet With Worksheets( sheet1 ) Set c = .Range(.Cells(1, C ), .Cells(1, C ).End(xlDown))...

Excel: VBA UnHide Row depending on variable, test sheet, command button
test sheet, command button, newby: Jimmy, according to your code, you want to unhide row 1 and all rows down and including the row number held in H11. If so: Private Sub CommandButton1_Click() Dim RowValue RowValue = Range( H11 ).Value Range( 1: & RowValue).EntireRow.Hidden = False...

Excel: View image within Word, image spreadsheet, 123456
image spreadsheet, 123456, allexperts: Something like this MIGHT do it for you - this however will clear ALL pictures, and requires a one cell table Sub Macro1() Macro1 Macro Macro recorded 14/11/2007 by Heather ActiveDocument.Shapes( Text Box 3 ).Select Selection.ShapeRange.TextFrame.TextRange.Select...

Excel: View Locked Cells on a spreadsheet, excel, find
excel, find: You can use the Find for that. Go to menu Edit- Find, open the Options, open the Format selection and on the protection tab mark the option locked. With that, you can click on Find All and it will list all the locked cells on the worksheet. The cells are...

Excel: VLOKUP PROBLEM, partial sentence, absolute reference
partial sentence, absolute reference, number column: Mark, There are several things which could be wrong. Most likely is that you have used the RANGE-LOOKUP value of 1 where you need zero. I d try that first. A good strategy is to assign a cell somewhere to hold this value, and then use...

Excel: VLOOKUP, IF, variable numbers, column c
variable numbers, column c, ascending order: Not sure how fixed the structure of the data is - the EASIEST way would be to build the lookup table with either an extra column, OR 3 fewer columns - such that you could have a unique value to match - for example, lets say you go with the extra column -...

Excel: Vlookup Problem, vlookup formula, ogilvy
vlookup formula, ogilvy, match: rubin, the lookup and match functions return a #N/A error when the item being looked for (usually the first argument in the function) is not found in the range being searched. You can trap this error with =if(iserror(formula), ,formula) Just...

Excel: Vlookup from another workbook - having trouble with the data update, macros, data sources
macros, data sources, xls: Kathy: The Short: VLOOKUP is a function that will work properly on a workbook that is closed. Thus, your scenario should work just fine. The Long: Unfortunately, your Excel isn t working the way it should...this means I cannot really answer the question...

Excel: vba macro help - creating new worksheets, sheet names, false application
sheet names, false application, vba macro: Sub CreateSheets() On Error Resume Next Application.DisplayAlerts = False Application.ScreenUpdating = False Set Curr = ActiveSheet For i = 3 To Range( A50000 ).End(xlUp).Row Err.Clear Worksheets.Add.Name = Curr.Cells(i,...

Excel: vba tip, column numbers, cell references
column numbers, cell references, simple matter: Mekdim, You could use VBA for this but if so, I can t help you because I don t offer VBA advice. However I would do this with a swath of ADDRESS and INDIRECT functions. Create a grid of ADDRESS functions in the shape you want your data....

Excel: vLOOKUP Formating, cell location, vba macro
cell location, vba macro, specifics: Hi Pradip, What you describe cannot be done with a worksheet function because these functions are not permitted to change any properties of a cell other than the Value property. The same is true of a VBA user-defined function (UDF). It is possible...

Excel: windows user name, holiday entitlement, cell a1
holiday entitlement, cell a1, ogilvy: Mark, if the username is on the left side of the list in say Sheet2 (assume in column A) in B1 of the sheet where the subject username appears as an example =if($A1 ,Vlookup($A1,Sheet2!$A:$Z,2,False)) in C1 =if($A1 ,Vlookup($A1,Sheet2!$A:$Z,3,False))...

Excel: How can I make this XL 2007 macro work for 7 more worksheets in the same book?, macro in excel, macro work
macro in excel, macro work, problem arises because: Sub sort() Dim i as integer Dim wks as Worksheet For i = 1 to 8 Set wks = thisWorkbook.Sheets(i) wks.sort.SortFields.Clear wks.AutoFilter.sort.SortFields.Add _ Key:=Range( A:A ), SortOn:=xlSortOnValues, Order:=xlAscending,...

Excel: XL , IF FUNCTION ,EVALUATING WRONG, douglas m smith, cell references
douglas m smith, cell references, cell values: Hi Mario, I suspect one of the cell values is actually 1 , a text value, instead of 1, a number. The way to tell is to formula cell, press F2 to edit the formula, and then highlight each of the cell references one at a time and press F9 to evaluate them....

Excel: ActiveX control causes "...reset project..." message on file open., combobox controls, project messages
combobox controls, project messages, vb environment: Without seeing/testing the actual workbook, I would guess this might work: Put in a Workbook_Open event code which simply has Application.DisplayAlerts = False If that doesn t work, and you d like me to take a look, feel free to email it to me at bobumlas@yahoo.com,...

Excel: Adding rows in a column automatically, blank rows, blank row
blank rows, blank row, undetermined number: Sub totalme() Dim rowcounter As Long Dim columncounter columncounter = ActiveCell.Column Dim endrowcounter, startrowcounter As Long For rowcounter = ActiveCell.Row To ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1 If Len(Cells(rowcounter,...

Excel: Advanced Excel sum of number of employees based on time of day, boss man, time cell
boss man, time cell, advanced excel: Jack, I thought so! Luckily, the main part of my answer is still completely correct. Start implementing it as I said, EXCEPT instead of referring to any one pair of the columns of start/end times, refer to a new column-pair which we will call the ...

Excel: Application Filesearch, novice user, floc
novice user, floc, excel sheet: It s hard to make it work given the current code doesn t seem to make sense -- look at this: If .Execute() 0 then For i=1 to .Foundfiles.Count PathInfo = .Foundfiles(i) FLoc = Standard_Forms_Dir & Fname Next i Else... The above...

Excel: Array Formula not working, array formula, sum function
array formula, sum function, p4000: Paul: Well, I cannot say for certain because an #N/A could be caused by a variety of things. First of all, the above array formula doesn t quite work the way you are intending. Instead, you should use the following: =SUM(([book1.xls]Stock!$P$2:$P$4000=...

Excel: Attainment Percentages, excel, if
excel, if: I cannot tell you if your boss is correct or not, as it depends on how the metric is defined and that is particular to each company. In any case, if you want to include an extra condition on transfers when target is zero, you could do something like: =IF(Adjusted...

Excel: Auto Populate Cells, mark monday, c101
mark monday, c101, a101: Ron, I think you need to take another crack at phrasing your question. There are too many things that don t make sense to me. Remember, I cannot see your worksheets and I don t know what your end goal is. For instance, though the equation you...

Excel: Auto-sizing of cells and comment boxes, dynamic size, issue question
dynamic size, issue question, empty space: ANY time you want EXCEL to do things automatically that it does not normally do automatically you will need to deploy macros that you have to write. And if you know anything about macro then I would suggest using userforms instead of the sheet itself to be...

Excel: Autofill in Excel, sheet test, speadsheet
sheet test, speadsheet, autofill: Pete, Try this: Private Sub CommandButton1_Click() Dim r2 As Range, r3 As Range Dim r4 As Range, r5 As Range Dim r6 As Range, bFirst As Boolean Dim LastRowInM As Long, r7 As Range Dim r As Range, ar As Range Set r = Range( A2:K2 ) LastRowInM...

Excel: Autofill form from database, vendor name, formul
vendor name, formul, ogilvy: Mike, I answered your second question within an hour of you asking it with specific examples oriented to the picture you attached. it looks like you just blew that answer off. Any ideas why it s always the same data? the reason you are getting the...

Excel: Autofilter based on Data Validation, cell c2, target address
cell c2, target address, data validation: Sarah, If you clear the cell C2 (make it blank), this will do it: Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range If Target.Address = $C$2 Then Set r = Me.AutoFilter.Range If Len(Trim(Target.Value)) 0 Then r.AutoFilter...

Excel: Autorun Macros, dear bill, davide
dear bill, davide, coop: After pressing the ENTER key EVERY TIME? This is not a good idea and you can t have this technique be turned off and then back on unless you do something manually to tell EXCEL to run the macro this time when I press the ENTER and then don t this time...

Excel: Auto_Exec, chip pearson, xls file
chip pearson, xls file, auto exec: Hi Martin, THe Auto_Open sub runs when you open the file it is stored in (in this case personal.xls), NOT when just ANY file is opened. Chip Pearson has a good tutorial on running code when a file is opened: http://www.cpearson.com/excel/AppEvent.asp...

Excel: Average_Excel, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, allexperts: Without reproducing the spreadsheet and trying it myself, I m going to guess that it s just a rounding error. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch my FREE 90-minute MICROSOFT EXCEL VIDEO...

Excel: add an excel graph in e-mail with VBA, outlook application, true body
outlook application, true body, vba: The chart object has an export method which enables you to export the chart as a graphics file. You can then use the Attachments.Add method of the outlook message to add the graphic as an attachment: With mailTender .Attachments.Add c:my documentsMyChart.png...

Excel: adding markup to excel sheet, csv file, seperate sheet
csv file, seperate sheet, question post: Andrew, If you mean you want to add that information to the csv file, then yes, I would see opening the file in Excel, putting in the formula to calculate the new column of values, then doing a save As using CSV as the file type. If you are just using...

Excel: arrange data in in the same columns, voice of experience, d price
voice of experience, d price, macros: You will not find a macro to do this, it will have to be written. 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...

Excel: array formula problem, excel, array
excel, array: It looks like you are trying to add the numbers on column N for the values that satisfy one of the first two conditions and the third one. You could try with some array formula like: =SUM(((MAIN!D6:D550= U )+(MAIN!D6:D550= SFU ))*(MAIN!K6:K550= GB )*(MAIN!N6:N550))...

Excel: Some assistance and clarification would be GREATLY appreciated., vb game, seperate issues
vb game, seperate issues, blank entries: David, I you bind the list of the combobox/listbox to the worksheet range, by using the rowsource property, then it seems to link to the underlying values of the time/dates which are serial numbers. What I have found to be effective is to load it using...

Excel: auto copy data between sheets, using ms office, header rows
using ms office, header rows, blank row: Sorry for the delay in replying - it took me some time to find the time to write the macro you need I m not sure how many of the top 3 rows are header rows, so at the moment I ve assumed that they ALL are - this macro will do the work for you. Sub testme()...

Excel: auto-populating cells, cell c2, ogilvy
cell c2, ogilvy, spencer: Spencer Assume A2: First Name B2: Last Name then C2: =$A2& &$B2 or =Concatenate($A2, ,$B2) If you lists of names going down columns A & B, then you could now select cell C2 and drag fill down the column Hope that is what you were...

Excel: automating sum(X,Y), excel, references
excel, references: With your first question, if you select the cell C2, go to menu Edit- Copy, select the cell C3 and go to menu Edit- Paste the reference will change, adjusting to the row. There are shortcuts to this, like the small square on the lower right corner, that can...

Excel: autosum in vista excel, autosum, point moves
autosum, point moves, relative number: Jack, First off I want to point out that when you say you want to add 0-15, 15-30, 30-45 you are adding 15 twice, 30 twice, and probably 45 twice. I hope what you really mean to say is you want to add 1-15, 16-30, 31-45, 46-60, etc. My answer assumes...

Excel: Borders in excel 2003., cell contents, b32
cell contents, b32, control c: Nabam, Perform a COPY by selecting the cell region and then hitting CONTROL-C, as you (probably) usually do. Then you can CLEAR the cell contents which will leave behind the borders. But then look up in the menu (or ribbon) and find PASTE SPECIAL....

Excel: Borders upon occurance of text in column, word change, question thanks
word change, question thanks, ogilvy: Harry, Looks like a typo on my part: Set r = Range(Cells(1, EH ), Cells(Rows.Count, E ).End(xlUp)) should be Set r = Range(Cells(1, EH ), Cells(Rows.Count, EH ).End(xlUp)) this also had a typo: Instr(1,cell, Change ,vbTextcompate) should...

Excel: Breaking down an address column, rt rt, column method
rt rt, column method, conditional test: Charles, What if someone enters and address as 1234 Carl Lake Shore (and omits the Drive ? You need to handle that case too. I can think of several ways to do this: Method 1) Create a table of all possible street types; Circle, Way,...

Excel: barcode scanner-to-excel, barcode scanner, excel spreadsheet
barcode scanner, excel spreadsheet, barcodes: Dimitris, You ll have to check with your barcode scanner supplier to see what kind of output formats the devices create. That s the first step. You need to be sure that whatever the output format is, that you can get it into Excel easily. I have...

Excel: beginner - using excel to calculate cost, free microsoft excel, validation form
free microsoft excel, validation form, excel tips tricks: Sure it s possible. You ll probably need to know how to use the IF function though if you have to make decisions based on the type of product (CD/DVD). See: You can use the IF function to have Excel place a value in a cell based on one or more conditions...

Excel: Calculate balance after autodrafts, auto expenses, auto values
auto expenses, auto values, days of the month: Joshua, I would have the calendar of dates already set up to show the days of the month on which these (and other) expenses are made. Have a separate column for auto-expenses . In that separate column, have an IF statement which compares todays...

Excel: Calculating formula from seperate worksheets based on sheet name, sheet names, worksheet functions
sheet names, worksheet functions, functions work: Lonny, Let s say that your workbook tab order looks like this (sheet names:) Totals LaborStart Labor1 Labor2 Labor3 LaborEnd MatStart Material1 Material2 Material3 MatEnd in a cell in sheet totals you could have a formula like =Sum(LaborStart:LaborEnd!B9)...

Excel: Calculating OT for the week, OVERTIME
OVERTIME: Josh: Lets assume your hours worked is located in cell A1. This formula will give you the regular hours: =MIN(1/24*40,A1) This formula will give you the overtime: =MAX(A1-1/24*40,0) Be sure to format the cells as time (XX:XX), just like the...

Excel: Calculating ongoing 3 and 12 month averages, cell a1, cell c3
cell a1, cell c3, roling: Hi Paul, Let s assume that Month 1 is cell A1 and 473.7 is cell C3. Your formula in cell C3 would be =AVERAGE(B1:B3) If you copy that formula down to C4 (by copying and pasting, or just draggging), it will change to =AVERAGE(B2:B4) and so on,...

Excel: Calling a Named Range in a formula, excel, indirect
excel, indirect, name: You can use the INDIRECT for that, something like: =COUNTIF(INDIRECT( &B1& !E:E ),F3) I am supposing that the cell in B1 contains the sheet name, and that is reflected on how the parameter of the formula is constructed. If you have the full reference...

Excel: Calling Variables in other sheets and workbooks, cell b3, daily numbers
cell b3, daily numbers, indirect address: Dan, If you have working formulas that retrieve the correct cell references, then you probably are having problems applying them because you don t know about the INDIRECT function. When you put a cell reference (such as the ones you are retrieving using...

Excel: Cell Fill Color, conditional formatting, 31st july
conditional formatting, 31st july, excell: You can use conditional formatting to do what you want but I can t supply details because I don t quite follow. Can you give precise example of 2 rows, say A2:E3 s contents, and what you d like to see highlighted, and why, and is the criteria specifically...

Excel: Cell Fill Color, a2 b2 c2, conditional formatting
a2 b2 c2, conditional formatting, color question: Sarwar, I cannot send you a fully-working schedule system for free. It will take me many hours to develop and this is how I make my living. If you want me to create a working schedule for you, it would become a fee-based project through my professional...

Excel: Cell Fill Color, conditional formatting, 31st july
conditional formatting, 31st july, excell: You would need to use conditional formatting to check the date in the cell against the dates - but not clear what rules to follow here - if you d like to clarify (my email if you want to send me a file is aidan.heritage@virgin.net) I m more than happy to help...

Excel: Cell Link, week 1
week 1: In another sheet of the same workbook or within the same workbook? Either way as long as you identify the names of the sheets and the full path of the other workbook, if applicable, then the link should work. Obviously since you have typed the link here...

Excel: Changing Borders or background color of the active cells in Excel, format cells, visual basic for applications
format cells, visual basic for applications, financial groups: Select the cell(s). From main Excel menu, choose format, cells, Patterns. On that popup tab, you can choose the background pattern of the cell. Then switch to the Border tab, and you can add borders of several different weights, styles, and in any color. ...

Excel: Character Limit in Word Drop Down Menus, drop down menus, sentances
drop down menus, sentances, character limit: WHY do you need to include lengthy sentances in a drop down? As the drop down control is an activeX control, I d simply use VBA to populate the document with the choice of text from there - otherwise you are going to be stuck with the limitations you have...

Excel: Check box insert, obox, cbox
obox, cbox, caption property: Alan, I am not totally clear on whether you want two macros or one macro. Also as far as getting status, I have shown how by displaying it in a msgbox, but I don t know what you actually want to do. do you want to count how many are true or take some...

Excel: Checkbox Macro Enabling, control toolbox, getting some errors
control toolbox, getting some errors, g6: Jarib, I didn t try to debug Daemon or your code beyond what you were asking. Change with Worksheet( sheet1 ) to With worksheets( sheet1 ) Yes, If you want the cells locked if checked then, was a typo on my part. If you check the...

Excel: Checking info against other worksheets in a workbook Macros, false selection, yy
false selection, yy, copy paste: You can add this above your End Sub: ProofLoans then have a separate procedure so you CAN run it independently: Sub ProofLoans() Dim n as Integer On Error Resume Next For i=1 to Sheets( Loans ).Range( C65536 ).End(xlup).Row n=0 ...

Excel: Close shared workbook, procedure declaration, ogilvy
procedure declaration, ogilvy, boolean: Carol, Sorry, since you were using workbook events, I assumed you were familiar with them and new how to get the declaration from the dropdown at the top of the module. (it is always best to let Excel/VBA enter the declaration in the module for you by selecting...

Excel: CONDITIONAL FORMATTING HELP NEEDED, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, excel tutorials: You just need to figure out the difference in days. Make this its own column, then change your conditional formatting accordingly. There are many different techniques in Excel you can use to break apart dates or times into their component parts, add or...

Excel: COUNTIF with data in two columns, excel, countif
excel, countif: Arthur... You can accomplish this using a nested if statement. If scores from A is in column A, scores from B is in column B and the pass rate is in column C, then the formula would be: =if(A2 =20,if(B2 =20, Result , ), ) Where result is what you...

Excel: Coding / if statment, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, paste link: You can pull data from one sheet to another by referring to it like this: =Sheet1!A2 This will allow you to reference data on other sheets. You can also Copy Paste Special Paste Link. Let me know if you have any other questions or comments....

Excel: Colored cells, colored fonts, colored cells
colored fonts, colored cells, format button: Select all the cells which you want to possibly change color. Use Format/Conditional Formatting. Change the dropdown which says between to equal to , then enter Buyer, then click the Format button, the patterns tab, and pick a green color. Then click OK,...

Excel: Colour AutoSUM, rcell, colorindex
rcell, colorindex, cell changes: Hi Stuart How are you? This is event driven programming: Your formula has to be recalculate whenever the colour of a cell changes. We have built-in events which run automatically a procedure(e.g. Private Sub Worksheet_Change(ByVal Target As Range in the...

Excel: Combined Formula, excel, if
excel, if: You could include the OR function to make a formula like: =IF(OR(A1=0,B1=0), Insufficient data ,IF(A1 B1,A1-B1,B1-A1)) This should give you the expected result. In this case, you could also simplify the formula a bit more with something like: =IF(OR(A1=0,B1=0),...

Excel: Comparable Sales Spreadsheet, column headings, excel expert
column headings, excel expert, property characteristics: Donald, Its difficult to make Excel retrieve multiple rows that meet multiple (or even single) criteria. It can be done, but trust me, you don t want to go there. You d have to become an Excel Expert to make it work. However, I see that you...

Excel: Comparing, colorindex, vba code
colorindex, vba code, conditional formatting: You d have a much easier time simply using Conditional Formatting. Please send a sampel wb to me at bobumlas@yahoo.com and I ll show yuo how to do it -- It s also better than VBA in that it s dynamic and you don t have to rerun the VBA code when something...

Excel: Comparing cells and then assigning score, false answers, student test
false answers, student test, correct answers: I m pretty sure this doesn t need a macro but can be done with a formula - IF I follow this correctly, you have (say) to columns of correct answers in row 1, then the student data on subsequent rows? This formula assumes that there are 10 questions, that...

Excel: Comparing multiple columns and conditions in Excel., interview dates, hiring manager
interview dates, hiring manager, heavy lifting: Kelly, Yes, there is a way. There are actually several ways. For Excel 2007: Use the COUNTIFS() function For Excel 2003: use the advanced (and undocumented) form of SUMPRODUCT. You can read about this at XLDYNAMIC.COM. For Either version:...

Excel: Comparing worksheets in excel, e mail address, countif
e mail address, countif, emails addresses: Jeff, If you want to write code, send me two dummy workbooks or data on two separate sheet with the layout of your data represented - the values can be dummies, but I need to know whete things are located so I know what to write in the code. You should...

Excel: Complet formulas for dynamic lookups, row labels, countif
row labels, countif, text don: Yolanda, What you want to do is quite possible, but I don t have enough information to get you started. I can only give you a general approach. Your question refers to specific cell ranges like D7 and D87-H88 but the image you provided doesn...

Excel: More Conditional Formating, conditional formatting, color border
conditional formatting, color border, cells: There are actually 4 - the format when none of the conditional formats are met + the 3 from the CF dialog. There is no way to increase the # of conditional formats aside from upgrading to Excel 2007. What can be done is VBA code to actually color the cells...

Excel: Conditional Formating, free microsoft excel, key performance indicators
free microsoft excel, key performance indicators, excel tips tricks: Sure, you would probably need an IF statement to determine the number of parameters that meet your requirements. You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE...

Excel: Conditional Formatting, conditional formatting, c9
conditional formatting, c9, ogilvy: Evonne, the today() function gives today s date. Dates are stored as the number of days from a common base date, so you can use a formula like =((today()-$B9)/7) 6 where B9 holds the start date, would be a test if the employee has been employed...

Excel: Conditional Formatting, excel, conditional formatting
excel, conditional formatting: Matthew, Lets assume your effective date is in A1 and your end date is in B1. In C1, you would type: =B1-C1, and format the cell as a number with no deciomal places. Next, choose conditional formatting from the menu or the ribbon in 2007, and choose...

Excel: Conditional Formatting, Cell Upon Cell, shape position, hand column
shape position, hand column, co worker: Doug, There is no function which detects the color of a cell and reports a value: so you can t say If cell X is Green, then do this.... Since you have applied conditional formatting, each of the cells changes colors based on some condition that...

Excel: Conditional Formatting While Referencing Another Worksheet, conditional formatting, drop down menus
conditional formatting, drop down menus, cell changes: Dave, I think you would want to have your code in the calculate event if you want it to react to changes in the source sheet. The Change event only fires when you actually edit a cell in that sheet. Also, since this should be firing when this sheet is...

Excel: Conditional formatting, excel, date
excel, date: Sorry for the late, I didn t connect during the weekend. Regarding your question, you can use a formula on the conditional formatting, so for example if the current cell is A1 and the cell with today s date is B1, you could set the formula to something...

Excel: Conditional formatting -> Charts, dynamic chart, pie chart
dynamic chart, pie chart, b15: Aaron, You could apply a FILTER or ADVANCED FILTER to your data table. Use the same criterion to display & hide the rows in the data table as you are using for the conditional formatting criteria. The chart won t show values for the rows that the FILTER...

Excel: Conditional formatting to match any cell in a range, log spreadsheet, construction project management
log spreadsheet, construction project management, conditional formatting: Robert, There might be a way to prevent typos from ever occurring. If you don t like this idea for some reason, write back with a followup and I ll explore your original question. My idea is to make the list where you might type a possible bogus...

Excel: Conditional sum of filtered range, colc, colb
colc, colb, ashutosh: Ashutosh Khiré: Well, it is a very complication formula. I will try to explain to the best of my ability, but please understand it is complicated (I won t be able to make it simple and easy). First of all I m going to shorten to the formula to a smaller...

Excel: Consolidate Data From Other Files, person checks, excel workbooks
person checks, excel workbooks, daily basis: Zohar, You can create links to any cell in any other workbook, which will retrieve the value of the cell in the workbook being pointed to. But with constantly changing workbook names and so on, it may be difficult to maintain this.... since there is...

Excel: Converting the date to number of days, date format. excel
date format. excel: Waheed... You can do this by subtracting the two dates and then converting the cell format to number. For example, if Jan-3-2007 is in A1 and jul-25-2009 is in B1, then in C1 type the following: =B1-A1 Then, right click on C1 and choose format...

Excel: Converting File path to Hyperlink, adnan khan, documents and settings
adnan khan, documents and settings, excel sheet: Make sure the MakeHyperlinks sub is available to the workbook because this routine calls it: Sub MoveBToA() on error resume next application.displayalerts = false for i=1 to range( B65536 ).end(xlup).Row n=instr(cells(i,2).formula, [...

Excel: Converting Times, string manipulation functions, free microsoft excel
string manipulation functions, free microsoft excel, excell format: You ll have to use string manipulation functions like LEFT and RIGHT to break that string up into something you can convert to a standard time format. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch...

Excel: To Copy ranges from multiple workbooks to one sheet, mycell, sheet index
mycell, sheet index, k15: Well, I still don t see a question, like How do I make the code do...? . All you tell me is what you need. Regardless, unless I am overlooking something, I don t see where your variable ShName that refers to a sheet named Form is ever used. Form...

Excel: Copy total from diff sheets to one, sheet names, double sum
sheet names, double sum, age population: eram, Assume the total cell is in F100 on each sheet If you have the 1000 cities entered in A10:A1009, then in B10 put in =INDIRECT( &A1& !F100 ) then drag fill down the sheet. If you need something to enter the sheet names or just to go...

Excel: Copying Excel formula using drag down option, relative reference, f4 key
relative reference, f4 key, excel formula: The formula you need is =D2/$B$1 to save typing the dollars, press the F4 key with the cursor on B1 in the formula editor area. This is an ABSOLUTE reference, i.e. it refers absolutely (or always) to B1, whereas =D2/B1 is a RELATIVE reference so changes...

Excel: Copying multiple ranges between two open workbooks, work books, sushma
work books, sushma, ytd: Sushma, Here is one way: (with some made up ranges since you only provided one pair) Sub Copy() Dim v1, v2 Dim i as Long v1 = Array( AD8:AE17 , M12:N20 , P1:Z13 ) v2 = Array( B6 , D6 , F6 ) for i = lbound(v1) to Ubound(v1) Workbooks( YTD.xls ).Sheets...

Excel: Copying from multiple sequentially named worksheets to one mastersheet within same workbook., claim numbers, blank row
claim numbers, blank row, bd7: Sorry for the delay in replying - took me time to find time to write the macro! and even so, this macro is the basis for the process, but I m not sure what determines which range you want to copy - is it the last used row on the worksheet for example? ...

Excel: Copying and pasting between sheets, string source, joe joe
string source, joe joe, private function: Joe, guess that is a new behavior/feature of xl2007 Public Sub copy_paste(source As String, _ source_range As String, dest As String, _ dest_range As String) Sheets(source).Range(source_range).Copy Sheets(dest).Range(dest_range).PasteSpecial...

Excel: Copying VBA module of sxcel to a text, chip pearson, spath
chip pearson, spath, web page address: Saranthi_57, Chip Pearson has extensive information on working with code using code. It is probably better that I share that web page address with you so you have a great resource available to you: http://www.cpearson.com/excel/vbe.aspx for you particular...

Excel: Count, free microsoft excel, attendance sheet
free microsoft excel, attendance sheet, excel tips tricks: Sure, just make an IF function to set a cell equal to 1 if your conditions are met. Then add those up. You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial...

Excel: Count the first instance of a value only, countif, pivot table
countif, pivot table, first instance: Your count is almost right but would return the same number at each instance of the organisation - so options are (1) use a pivot table to group the data together - but as you only want to count the organisations, possibly overkill (2) use data, filter,...

Excel: Count If function with filter, pivot table, workaround
pivot table, workaround, nate: Nate, I created a quick workaround for this, but I don t know if you ll like it. With the filter in place, rows will be hidden, as you know. So apply the filter so rows are hidden. Take a spare column to the right of the database (or even insert...

Excel: Count multiple columns in excel, sumproduct, males and females
sumproduct, males and females, sex male female: Rusty, Write a followup that includes the failed equations, and the results they reported. I can t tell what you did wrong unless I can see your work. However, if you are trying to COUNT things you should use COUNTIF(), not SUMIF(). That s...

Excel: Count unique data in unfiltered rows, counta, pivot table
counta, pivot table, occurances: Chris, Please start over as I don t understand what you re getting at. There are several things in your statements that puzzle me. You said: ....when I use a filter on it I need the numbers to change. A FILTER does NOT change the numbers...

Excel: Counting with check boxes, check boxes, excel spreadsheet
check boxes, excel spreadsheet, full contact: I THINK I d be inclined to have a small macro, attached to a button on the spreadsheet, which when clicked increased the value of a cell sub IncreaseMe range( b1 ).value=range( b1 ).value+1 end sub is all the sub needs to be - obviously replace B1...

Excel: Counting colors, blue indigo, color word
blue indigo, color word, simpler method: Stuart, Are these colors actually cells formatted as the colors, or are they the words RED, BLUE, INDIGO.... etc, typed into the cells? If they are the cells formatted as the colors, I don t know of any way to count them. if they are the words,...

Excel: Counting duplicates and listing the rows they appear in., computer name, pc1
computer name, pc1, different computer: Per, The reason you have this problem is because you have designed an unworkable data structure. It would be much better to re-create your database so it is a real database that you can ask questions of. What I would have done is make columns called...

Excel: Counting green digits., len function, vba code
len function, vba code, colorindex: This works on one cell at a time: =CountByColor(A1) Function countByColor(rg As Range) Application.Volatile True If rg.Cells.Count 1 Then countByColor = 0 Exit Function End If n = 0 For i = 1 To Len(rg) ...

Excel: Counting instances of "" depending if crityeria from another cell is met, evaluation score, double negatives
evaluation score, double negatives, pivot table: You COULD possibly use a pivot table to analyse the data (would give you all the results) but a multiple condition COUNTIF can be done like this =sumproduct(--(Evaluations!$AE$2:$AE$1000= What to do next ),--(Evaluations!AF2:AF1000=3)) The double negatives...

Excel: Counting number occurences of the data seen after a filter has been applied., excel, fiter
excel, fiter: There is no single formula that can do what you want. For example, you can use SUBTOTAL to aggregate only the visible rows on a column with a given operation, but you cannot include conditions on that formula. For example, a formula like this will count...

Excel: Counting times, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, looking for a new job: Use IF to create a new value in a second column that s either 1 or 0 if the time falls in your range. Then COUNT that column at the bottom. You can use the IIF function to perform If/Then analyses on your data. Please see the following FREE video tutorial...

Excel: Create summary from a sheet that takes data from sheet that's calculated based on a drop-down cell, cell a1, customer name
cell a1, customer name, variable value: Shradda, This is called a SCENARIO. I haven t used this feature very much so I can t give you the details. But the idea is that you: a) feed in a variable value from a list of values: this would be the customer name b) the workbook calculates...

Excel: Creating a quote from an excel spreadsheet, spreadsheet problem, quotation request
spreadsheet problem, quotation request, quantity column: If I understand the question right, you can use data/Filter/Autofilter, and on the dropdown for the quantity column, you can filter by items 1, then you can select all the visible items (alt/; or goto special/visible cells) and copy them and paste to another...

Excel: Creating a variable that would make it easier for the macros, addre, drag down
addre, drag down, dest: Hi Shawn How are you? What about this? Sub Drop_Down() ActiveSheet.Unprotect Password:= 1234 Drop_Down Macro Macro recorded 3/26/2009 by shawnh Dim orig As Range, addre As String, dest As String addre = InputBox( Range from which you want...

Excel: Currency symbols, format cells, currency symbols
format cells, currency symbols, seminar book: The problem is, there s no real $ in the cells, it s only in the formatting. I m afraid you can t do a global edit/replace... you do need to unprotect, select the cells with currency & replace the formatting. Sorry -- no magic bullet here that I know of...

Excel: Custom cel format, free microsoft excel, format cells
free microsoft excel, format cells, excel tips tricks: Convert the cells ahead of time to TEXT (right-click, format cells) or prefix each item with a single quote. 10-41 This will force it to TEXT. Let me know if you have any other questions or comments. Also, be sure to visit my web site and watch...

Excel: calling macro when cell value changes, target address, drop down list
target address, drop down list, spreadsheet: Say you want this code to run when the changed range is entirely within col A (as when user changes a dropdown in that col), but not run if the changed range goes beyond col A (as when user deletes an entire row). The code whould be like this: Private Sub...

Excel: Getting a cell to reference a table, week 1, vba
week 1, vba, aidan: If you want something to update by clicking, it will need some VBA to achieve it - possibly easier would be to use a filter based on a dropdown selection - it s quite hard to read from the picture exactly what you are trying to do but if you d like to email...

Excel: change cell reference number by an n-amount, blank rows, cell reference
blank rows, cell reference, reference number: The INDIRECT worksheet function will convert something that looks like a reference into a reference so =indirect( D & 1293+35) would equate to =D1328 HOWEVER, you would still have to work out how to get the 35 and 1293 in place - perhaps using...

Excel: changeable data validation list range, data validation, reference example
data validation, reference example, dynamic range: I would do this with a dynamic range - defined by insert, names - the name can be anything, but would be =INDIRECT( FORM!$C$40:$C$ &39+COUNTA(ServicesRows)) Your concatenate is fine, but can be done the same with & signs, and as you can see, didn t need...

Excel: changing the colour of a number based on result, vlookup, b14
vlookup, b14, spreadsheet: Marcus I m guessing that you want to know how to make the cell format when the number is positive. You can do this by making the cell with the formula in it the active cell, then go to toolbar/format/conditional formatting/ select cell value is , select...

Excel: changing columns and sorting, excel, cut
excel, cut, sort: Sorry for the late, I have been busy on the weekend. To change the column Q to the front of the spreadsheet, you can select the full column by clicking on column header, go to menu Edit- Cut, select the full column A, right click and select Insert Cut Cells...

Excel: Make changing data static, audit schedule, max column
audit schedule, max column, audit management: Steven, This is a particularly vexing problem for Excel, but perhaps in your case there is a workable solution. How about if you add ten columns and keep a rotating history of the audit dates in them? The in an 11-th column take the MAX of the...

Excel: changing font letters from Capital letters to small letters, cell a1, excel sheet
cell a1, excel sheet, capital letters: Abdul The formula LOWER(A1)will change the value of A1 from upper case to lower case. You can apply this formula form one sheet to another to copy and convert the text. The formula =LOWER(Sheet1!A1) would copy the value in sheet1, cell A1 to the formula...

Excel: chart problem, isnumber, carrier name
isnumber, carrier name, dear bill: Sami, I can t tell you how to do it right until I know how you are doing it wrong. What is failing? What results do you get? Your problem is NOT due to filtering or conditional formatting. The chart will display only the data that the filter...

Excel: checkboxes, control toolbox, check boxes
control toolbox, check boxes, text boxes: Phil, The easiest way to do this is to have a macro delete any textboxes and then put in new text boxes according to your specifications. the code you show Loops through all the shapes in the worksheet (whether a textbox or not) and then as they are...

Excel: code to automatically hide rows, couple versions, autofilter
couple versions, autofilter, pivot tables: Elliot, You could select the column and do data= Filter= Autofilter and select the empty rows in the dropdown at the top of the column. This will hide the empty rows (in xl2007, the filter is the second to last button on the home ribbon). There...

Excel: Two columns into one in Excel, row move, do loop
row move, do loop, macro recorder: Laura, This actually quite difficult to do unless you write a simple macro to do it. You could try to write one yourself using the Macro recorder. This simple macro would perform ONE of the operations you need... insert row, move data (relatively)...

Excel: Two columns into one in Excel, vba, cells
vba, cells: You could do it with VBA, or possibly easier with a simple formula - which would go in a third column the first cell has =OFFSET($A$1,INT(ROW()/2),0) the second =OFFSET($B$1,INT((ROW()-1)/2),0) Select these two cells and then drag down to auto...

Excel: columns on scatter chart?, scatter chart, vertical columns
scatter chart, vertical columns, time scale: You CAN do it -- make sure the top of your x-values is blank and the top of your Y-values are labelled. Make your column chart, then make the scale a time-scale x-axis (which gives it the correct spacing), then format the x-axis as General. You need more details...

Excel: combining date & time from two columns, air quality reports, automatic conversion
air quality reports, automatic conversion, zero times: Renee, You ll LOVE how easy this is! All you have to do is ADD the DATE and the TIME with a simple PLUS! Then format the result as a date & time combined. This works because the DATE & TIME are stored as a specially-coded serial number....

Excel: compare two lists in excel, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, vba programming: This is VERY difficult to do just in Excel. In Microsoft Access you could run something called a FIND DUPLICATES query, but in Excel you would require some VBA programming or a macro. Let me know if you have any other questions or comments. Also,...

Excel: compare time values, vba macros, time values
vba macros, time values, excel sheet: I get 0 for =Closetime(A1,B1:B2) because there is no time BEFORE the value in A1 in B1:B2. I don t get #Value. Where are you putting the function? It needs to be in a module. It IS automated -- you need only put the function in the cells where you want the...

Excel: conditional formating, conditional formatting, countif
conditional formatting, countif, menu choices: Sami, select B1:B20 with B1 as the activecell. Do format= Conditional Formatting then change Cell Value is to Formula is and put in a formula like =countif(Sheet1!$A:$A,Sheet1!$B1) change the sheet name to match you sheet. then click the format...

Excel: conditional formating and matching multiple, skill code, change colors
skill code, change colors, skill type: Laura, Its possible but somewhat involved. Read about the MATCH and INDEX functions and be sure you understand how these work, because they are the key to solving this. I presume that you want to lookup the number FROM the bigger table and have...

Excel: conditional formatting, excel conditional formatting, html excel
excel conditional formatting, html excel, written description: Theresa, This would be something that could be done with the conditional formatting feature of Excel - since that is you subject - it appears you know about it - so I am not sure what you specific question about the feature is. Fortunately there is a web...

Excel: convert text to .xyz, folder structure, file extension
folder structure, file extension, windows explorer: Rosli, I have never heard of a file extension of XYZ that means anything. So the only thing to do is SAVE AS, specifying whatever type you want the file to be, then, exit Excel, find the file in your folder structure and RENAME it (using Windows...

Excel: To make copies of sheet, exact scenario, list of departments
exact scenario, list of departments, excel workbook: Sub MakeSheets() Application.ScreenUpdating = False Set main = Sheets( Main ) Set newwb = Workbooks.Add On Error Resume Next For i = main.Range( A65536 ).End(xlUp).Row To 2 Step -1 ThisWorkbook.Worksheets( Info ).Copy After:=newwb.Sheets(Sheets.Count)...

Excel: copy first character in a cell, excel, data
excel, data, format: Probably Excel is converting the values into dates, so you may use YEAR instead of LEFT to get the value. Something like: =YEAR(A1) My suggestion in this case, if you want to store text, is to format the column that will hold the data from general to...

Excel: copy to list, target value, target range
target value, target range, hlookup: Chris Right click on the sheet tab of Sheet1, select view code and then paste in a macro like this Private Sub Worksheet_Change(ByVal Target As Range) Dim r As Range, r1 As Range If Target.Count 1 Then Exit Sub If Len(Trim(Target.Value)) = 0...

Excel: copying cell color, conditional formatting, cell image
conditional formatting, cell image, color question: Pravin Assume the conditional formatting you show is for cell C9 and the text is in B9. in the conditional formatting dialog with B9 selected, you would change the cell value is dropdown to display Formula is for each condition and in the resulting...

Excel: copying hyperlinks, excel, cell
excel, cell: You are right, as I wrote the formula it repeats the name of the last sheet that was evaluated. Try this way: =CELL( filename ,A1) This should make it work for the current sheet every time. Sorry for the earlier mismatch. Hope this helps, Migue...

Excel: count the number is instances of two conditions, array formula, sumproduct
array formula, sumproduct, commercial markets: Kristin, Yes, there is a way. There are actually several ways. For Excel 2007: Use the COUNTIFS() function For Excel 2003: use the advanced (and undocumented) form of SUMPRODUCT. You can read about this at XLDYNAMIC.COM. For Either...

Excel: counting cells and conditional formatting, conditional formatting, red cells
conditional formatting, red cells, logical tests: You are right that there isn t any easy solution to this, BUT as you are using formulas to set the colours, the EASIEST way would be to use these formulas again to get the count - what I don t know is how complex the formulas are, but I m guessing we have...

Excel: counting cells and conditional formatting, conditional formatting, red cells
conditional formatting, red cells, background color: Rather than a function which would do it (there is nothing builtin -- you d need VBA code), you can use a function which tests the same conditions which make the cells red and another to test the same conditions which make the cells green. I can t help without...

Excel: counting cells and conditional formatting, conditional formatting, chip pearson
conditional formatting, chip pearson, bob phillips: Al, the easiest approach is to count the cells using the condition in conditional formatting. Since you have two columns, I would assume that the formulas are the same for all the cells - so you would just count based on the conditions in that formula....

Excel: creating records using vb, input value, value move
input value, value move, true range: James, First: As I said I broke the line into two lines to make it **easier to read in the email** so it wouldn t word wrap - however, I made it syntactically correct so it could be copied and pasted if it was. You don t have to break it into two lines...

Excel: how to cross duplicate? cross compare, excel, cross compare
excel, cross compare: This is a solution with formulas, and two extra columns. In this case I have entered the original data on range A1:B6 (with headers) on sheet1, and start the desired layout on sheet2 cell C1, with the two first columns reserved for extra formulas. With that,...

Excel: cross-sheets reference, change references, vladimir
change references, vladimir, a4: Vladimir The reference is to the cell, not the value in the cell, so when the values change in the referenced cell the linked values change. I can t think of a way to fix the link to the value. If you do your sort in the cells with the links in them...

Excel: cut and paste protected sheet, vba script, mouse type
vba script, mouse type, design criteria: Libby, Maybe something like this. Sub CutSelectedRow() Dim r as Range, r1 as Range With Worksheets( Main Excel ) If Selection.Parent.Name = .Name Then check to make sure a row is selected set r = selection On Error...

Excel: Data Validation list, data validation, a15
data validation, a15, ogilvy: Gloria, In excel 2003 and earlier, if you do Insert= Name= define, then select this name, in the refers to box it should have something like =Sheet1!$A$1:$A$10 If you put in additional entries in A1:A15, you should change this to include them. Click...

Excel: Date formatting, cell reference, text values
cell reference, text values, questioner: Greta, 1) I asked you to enter =ISTEXT(date-cell) and =ISNUMBER(date-cell) If you got NAME you must have done this improperly. read the function reference on how to use these two functions....

Excel: Date related problem, vlookup formula, dear tom
vlookup formula, dear tom, value error: Sami, If should be a matter of just doing an addition. i assume in C2 =A2+B2 format the cells as DD/MM/YYYY dates are stored as date serial numbers - the elapsed number of days from a base date. In Windows, the default base date is 1/1/1900...

Excel: Decreasing ComboBox Items, vba code, ogilvy
vba code, ogilvy, test 1: John, Use the click event of the combobox and after writing the selected item to whereever you are going to use it, then use the removeitem property of the combobox. It is easier for me to provide some sample code. Obviously you will need to write...

Excel: Deleting Blanks Macro Question, blank cells, first error
blank cells, first error, error handler: Jack, I saw the suggestion on the Application.CountA. I can t think of an occasion where that wouldn t work unless you have merged cells in the selection - then it might have problems - haven t tested it - but merged cells seem to cause problems with everything....

Excel: Deleting Cells and Parsing left over information, 7 digits, spredsheet
7 digits, spredsheet, internet explorer: The pictures always come through very small on AllExperts, so it s almost impossible to see what you need to do - I assume a web query wouldn t do it for you - is it possible to see some sample data in an actual sheet? My email is aidan.heritage@virgin.ne...

Excel: Deleting Duplicate Records based on 2 columns, duplicate records, question question
duplicate records, question question, column c: Your clarification didn t really clarify - I MEANT to ask but probably failed to do so, what constitues a duplicate record - is it when C and K are the same as another C and K? IN essence, the process is easy - either a delete if or an add if condition -...

Excel: Deleting Duplicate rows using 2 columns, logical test, column c
logical test, column c, macro code: Well spotted - I had in my head this was column A, and wrote the macro accordingly, then realised my error and corrected the starting cell but not the rest As to the code - this one is ALMOST readable as is basically, we need to keep a note of the current...

Excel: Deleting a row of data without deleting entire row, swift reply, question thanks
swift reply, question thanks, data changes: Alan, Sorry but I don t understand what you want to do. If you wanted to check the A162 to F261 on sheet3 and delete the rows in that section if any cell in the row contains #N/A produced by a formula I would do Sub ABC() Dim r As Range, r1 As...

Excel: Deleting rows considering two different columns, duplicate records, case c
duplicate records, case c, column c: Sorry, we re still not on the same page. If this is the case: C K 12345 02/05 12345 03/01 should one of these rows be deleted? (If so, which?) or, is this the case: C ...

Excel: Dependent Dropdown, excel, dependent list
excel, dependent list, camera: For the data validation, here is a link that explains a step by step process: http://contextures.com/xlDataVal02.html If you have any issues following it please let me know. Regarding the additional option, if you want to show the picture of the item...

Excel: Determining the existence of a sheet in a workbook, rng, boolean
rng, boolean, b2: Hi Quirino How are you? Please have a look at the following code which should preceed Dim i as Range of your procedure: Sub insertDelta() Dim i As Range Dim rng As Range, s As Worksheet, f As Integer, enc As Boolean f = 1 enc =...

Excel: Difference between Average and Sum/number in Excel, excel, sum
excel, sum, average: You answered my next question, because the most common reason of difference between those formulas are the empty cells. In this case, my recommendation would be to use the same range on both formulas, just in case there is any extra sheet considered on the...

Excel: Difference between First and Last date for same event, array formula, last date
array formula, last date, ogilvy: James, in D2 =IF(COUNTIF($A$2:A2,A2)=1,MAX(IF($A$2:$A$1000=$A2,$C$2:$C$1000))-MIN(IF($A$2:$A$1000=$A2,$C$2:$C$1000)), ) Entered with CTrl+Shift+Enter rather than just enter since this is an array formula. Then drag fill down next to your data. ...

Excel: Drop-down menus and additional sheets, drop down menus, vba code
drop down menus, vba code, drop down menu: It would need to use VBA running on the change event of the worksheet - though if using VBA I d be inclined to use a userform to get the data and populate it rather than hiding and unhiding sheets. If you would like to email me a sample file I can insert...

Excel: Dropped down menu and coloring a field, f2 g2, data validation
f2 g2, data validation, drop down menu: Assuming you mean by a drop down menu that you have used Data Validation, then you will need to deploy Conditional Formatting. You don t make it clear because you did not say if you want these cells to be a DIFFERENT color based on the selection made and/or...

Excel: Dynamic Graphs, dynamic graphs, xl2000
dynamic graphs, xl2000, dynamic range: Bill, I always use a defined name (with offset) as the source for the series when I want to have a dynamic range for the series. I suspect you could I tested on both xl2007 and xl2000 on putting in a formula directly as the source for a chart and I...

Excel: Dynamic Sheet Access, sheet names, sales ledger
sheet names, sales ledger, d7: You can put the sheet names in separate cells, like E1 & E2 or get the day of the month in E1&E2 by formulas, then refer to E1 & E2 in your formulas using indirect. For example instead of =SUM( 7:13 !B49) you can use =SUM(INDIRECT( &E1& : &E2& ;!B49 ))...

Excel: dashes!!!, isnumber, question title
isnumber, question title, proper answer: Annie, I need to know what these numbers ARE. Are they TEXT or are they NUMBERS? Pick one of the FAX numbers and write two equations nearby. If Cell F30 has one of these FAX numbers in it, then write ( in two cells, somewhere nearby), ...

Excel: data macro, voice of experience, macros
voice of experience, macros, fac: To setup the macro would basically be to write it - there is not much difference. 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: "date" formatting in mixed cell, help file, formatting
help file, formatting, cells: Elliot, Each time you &-in a number, embed the number inside a TEXT function. The second parameter of the TEXT function is a format code, which you can copy directly out of the formatting dialog box. The help file on the TEXT function will step-by-step...

Excel: How to differentiate file name and extract out data to paste in master file, cell a2, cell a1
cell a2, cell a1, c test: Hi Gimsiew, I assumed you meant in the next column as opposed to the next row, otherwise you d soon end up overwriting values (A2 becomes A3 becomes A4 and next becomes A5, where another value already has been entered). Use this VBA macro (run ProcessFiles,...

Excel: I am having dificulty applying your fix, positive integer, cells
positive integer, cells, hmmmm: Milton, Other people s worksheets are rarely self-explanatory . Which number (and I want you to name the EXACT cell!) do you want to ttansfer to where (name the EXACT cells!) ? I understand whatever the number is, you only want to transfer it...

Excel: How to disable editing while multiple sheets are selected?, false application, group mode
false application, group mode, target: For each workbook you want to ensure this doesn t happen, right-click the excel logo (near the file menu), select view code, put this in: Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If ActiveWindow.SelectedSheets.Count...

Excel: disable message, dear bill, better solution
dear bill, better solution, suggestion: That all depends on how you have written the code and I have not seen the code to make an exact suggestion. You can trap for it implicitly using On Error statements or explicitly by using a series of IF statements. You will need to choose which is easier...

Excel: drop menus and matrix, drop menus, matrix rows
drop menus, matrix rows, index function: Drew, Certainly it is possible! What you need to do is make a matrix of all thew possibilities so you can look up the value you need from the appropriate intersection. This involves use of the MATCH & INDEX functions. So set up a table...

Excel: dropdown, data validation, column headings
data validation, column headings, column title: Octavio, Here is one of my methods for doing this. 1) make a table whose column headings are the names of each drop-down list you want. The first column should be the main one, call it MAIN. Populate it with the words you want to see in...

Excel: duplicate entries, duplicate sales, reseller name
duplicate sales, reseller name, sumproduct: Richard, in the next available column say column 51, so in cell AV2 =sumproduct(--($B$2:$B$5000=B2),--($F$2:$F$5000=F2),--($M$2:$M$5000=M2),--($AA$2:$AA$5000=AA2),--($AM$2:$AM$5000=AM2)) then drag fill down the column. Cells showing a number greater...

Excel: dynamic percentages, target weight, column c
target weight, column c, excel sheet: Charles Assume the weights (and only weights) are in column C starting in C1 Assume the target weight is in D1 then say in E1 Using your formula modified to get the last value in column C: =(OFFSET($C$1,MATCH(9999,$C:$C,1)-1,0)-D1)/D1 format...

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

Excel: Editing sheet values with a custom made form, stock range, price sheets
stock range, price sheets, specification sheets: Mitchell, It should be E1 instead of A1 - must have been a typo on my part - I have it searching column 5 (column E), so the After argument must be in the range being searched. I don t see how it could delete anything. there is on code in there to...

Excel: Entering data in excel that will result in specified data appearing in another cell, cell b2, vlookup
cell b2, vlookup, saleman: Sure. If the service center is in column C: =VLOOKUP(A1,Sheet2!A1:C50,3,FALSE) basically: =VLOOKUP(ValueToLookUp,RangeToLookUp,WhichColumnInThatRange,FALSE) the False means you want an exact match. Something you wouldn t want if you were looking...

Excel: Entire Row Selection on a specifc criteria, coloured cells, conditional formatting
coloured cells, conditional formatting, cell interior: Azeem I guess by specified you mean you will select the cells/rows that you want it to look at: sub SelectColoredCells() Dim cell as Range, r as Range for each cell in selection if cell.Interior.ColorIndex = 6 then if r is nothing then ...

Excel: EXCEL, hyphen, parenthesis
hyphen, parenthesis, cursor: =SUBSTITUTE(SUBSTITUTE(A1, ( , ), ) , _ ) will do this for you where A1 is the cell you want to alter - put this in a blank column, then drag down, copy and put the cursor on the first REAL cell - use edit, paste special, values to overwrite the original...

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

Excel: Excel 2000, data validation, absolute references
data validation, absolute references, max lines: Dora, You have to use the decimal number validation, NOT the custom . Custom doesn t provide the MIN/MAX dialogs, which is where your IF statements must go. The statement you put into the MIN or MAX boxes must begin with an equal sign. If I...

Excel: Excel 2003, excel, header
excel, header: As far as I know, you cannot frame it within the header/footer section on the Page Setup. You probably know this by now, but my suggestion would be to format the first rows on the spreadsheet as you wish, and then repeat those rows. Hope this helps, Migue...

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

Excel: Excel 2003, vlookup function, absolute reference
vlookup function, absolute reference, list of names: Chris, For each value that you want to pull, use a VLOOKUP() function to retrieve it. The account num is the lookup-value, the main database is the table array, each value needs its own col-index-num, and the range-lookup,should be zero. Be sure...

Excel: Excel 2003 - Autocomplete, autocomplete, autocorrect
autocomplete, autocorrect, question thanks: Janet My apologies. You can t edit the list as it is taken from the preceeding cells, if you remove them from the list they wont show in the autocomplete. If you want to turn of the list have a look at this link http://support.microsoft.com/kb/263552...

Excel: Excel 2003 data entry, excel, enter
excel, enter: I would keep the ENTER key in the default option, and change the routine a bit to finalize the first and second column entry with TAB. This will make the cursor go to the right, and when you ENTER on the third column it will go to the beginning of a new line,...

Excel: Excel 2003 Formula, blank answer, f2
blank answer, f2, c2: Chad, Oops, I knew I should have re-read your question! =IF(OR(ISBLANK(c2),ISBLANK(f2)), ,IF(f2-c2 90, Yes , No )) You didn t say what you wanted if the days were NOT less than 90 days apart, so I made an equation that would say NO for...

Excel: Excel 2003 VBA to identify unmatched non-unique values, colour values, menu format
colour values, menu format, g data: Credits are in col A, Debits are in col B, and you want to match them up. First select Cols A:B, then choose from main Excel menu Then choose from menu Format, Conditional Formatting, Formula Is, =C1 0, Format, Patterns, [Yellow], OK. You say in your Subject:...

Excel: Excel 2004 v11.3 for Mac, countif, excel project
countif, excel project, result table: David, It would seem that I recently answered a very similar question like this. What s up with that? Anyway, I d do a COUNTIF() of each letter, for each column. Then apply the RANK function to the COUNTIF results. Or, you could SORT the COUNTIF...

Excel: Excel 2007 date calculation and formatting, logical function, conditional formatting
logical function, conditional formatting, undefined value: Pat, Excel always knows what date and time it is. Just put the TODAY() function in a cell and it will report the date. So you can add/subtract the current date from any other cell containing a date to find the number of days between dates, or you can...

Excel: Excel 2007 Smart tags, tag option, smart tags
tag option, smart tags, smart tag: Russell I can t seem to reproduce your problem, and of course that makes it difficult to solve. I haven t experienced the problem before. One thing you might try is copying the data to another spreadsheet that has the smart tags feature turned off,...

Excel: Excel 2007 Template, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, vba programming: No way that I can think of that doesn t involve some VBA programming. Sorry. 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: Excel 2007 - VBA programming, seperate cells, inputed
seperate cells, inputed, ddo: Yes, I m happy to do VBA - but first I d need a little more information - is it always the number 5 you are looking for, an ddo the results need always to be in seperate cells? Also, you have 3 returns - is it always three returns? It may help to mail a...

Excel: Excel 2007 version, ancestry com, deleting files
ancestry com, deleting files, document names: If you use the FIND option, then you can select a file from this list and delete it (click it once, hit delete, or right click and delete from here) - that moves it to the recylcle bin, so you can empty the bin to delete it - holding down shift when hitting...

Excel: Excel 2007 version, ancestry com, kind of virus
ancestry com, kind of virus, excel expert: Wynneth, You said: I guess I should have 5,000+ lines of information. What? Where? In an Excel spreadsheet? I thought having 5,000 line is the Problem, not what you should have. I m confused. You said: The list is the DOCUMENT LIST...

Excel: Excel, homework problem, sumif
homework problem, sumif, countif: Mohammad, Because this sounds like a homework problem, I ll teach you how to solve it, but I won t solve it for you. It would help to know what Excel topic you were studying at the time, because that is probably what your teacher expects you to apply....

Excel: Excel, excel, time
excel, time: It depends on how you are storing the hours/minutes worked. If they are stored as time, a formula like this can be enough: =10*A1*24 Where 10 is the hourly rate. If they are stored like minutes, then you can change the formula to: =10*A1/60 If the values...

Excel: Excel, format cells, category 5
format cells, category 5, b1: Hi Dato How are you? Please do the following 1. Type in A1 =today() 2. Type in B1 10-07-09 3. Type in C1 =B1-A1 4. Format C1 to store a number e.g. Excel 2007 Cells - Format cells Number in Category 5. Save the workbook Cheers Adelaide...

Excel: Excel, unit rosters, medical status
unit rosters, medical status, e mail: SOUNDS perfect for a pivot table (but I don t know the layout of the data but if it s sortable, it s likely a good candidate) -- where you can put the unit rosters as a page field and you can use the built-in feature to Show Pages of the page field which...

Excel: Excel, current value, aidan
current value, aidan, b2: I m not sure what you are doing at the moment to get the values to go across to the two cells, but an IF statement would seem to be the way to go - I ll assume that Total Mileage is A1, 40p is B1 and 25p B2 in B2 =if(a1 =10000,a1,0) and in B2 =if(a1...

Excel: Excel, cell a2, olkahoma city
cell a2, olkahoma city, formula index: I ll assume both tables are on same sheet, starting in row 2, so that cell A2 is Stillwater, cell F2 is Oklahoma City, etc. In K2, enter: =A2&B2&C2&D2 and copy down col K as far as your data goes. You may white-font or hide this col if you d like....

Excel: Excel, colr, case white
colr, case white, vba code: I wouldn t (probably) use VBA for this - I d simply use a lookup table with the vlookup worksheet function. For VBA, the easiest way would be a select case function ReturnValue(Colr as string) select case lcase(Colr) case red returnvalue=1 case ...

Excel: Excel, excel application, client input
excel application, client input, formulae: Brian, I can think of several possible solutions. 1) when you protect a sheet, Excel offers you a list of checkboxes as to what you can allow the users to do. One of them is Edit Objects . Check that one. 2) put the charts on their own, separate...

Excel: Excel, separator character, excel spreadsheet
separator character, excel spreadsheet, data group: Naomi, This is a very common problem that has numerous solutions. 1) First thing to try is RELOADING the data into Excel in a different manner. If you imported this info as a TXT file, try importing (opening) it again, specifying a comma as the...

Excel: Excel, excel, match
excel, match: Use a formula like this on cell B2 of sheet1: =NOT(ISNA(MATCH(A2,Sheet2!$A$1:$A$10879,0))) I am assuming that the data is on column A, and that you have headers. This will return TRUE if the value is on the list and FALSE if not, copy it down the 800 numbers...

Excel: Excel ActiveWindow.ScrollRow, VBA Range
VBA Range: Hi Eric, Yes this is, but you need to pass the named row s row number to the command as part of the process: ActiveWindow.ScrollRow = Range( WhateverYouNamedYourRow ).Row Doug Smith Brainbench MVP for MS Excel www.brainbench.com www.abudant-solutions.com...

Excel: Excel Add in, aidan, macros
aidan, macros, heritage: Not easily - but if you record the macro, you can put it within the section I outlined - eg for each sh in activeworkbook.sheets sh.range( A1 ).value=1 next would set the value of A1 on all sheets to 1 - in other words, prefixing the macro you...

Excel: Excel Autocomplete & =value(), members name, autocomplete
members name, autocomplete, vba: see http://www.ozgrid.com/Excel/autocomplete-validation.htm for an explanation of the first part - as to the second part, I think you would need some VBA to achieve it - I m happy to help out with this if it helps, but would be useful to see the sheet...

Excel: Excel Autosum within a Macro???, autosum, d test
autosum, d test, two rows: Staci, if total will be in your last row in column A for example Dim r as Range set r = cells(rows.count,1).End(xlup) r is now a reference to last used cell in column A if instr(1,r, total ,vbTextcompare) = 0 then msgbox Total...

Excel: Excel array question, question question, a11
question question, a11, karel: Hi Jason, Pfew, still not entirely getting me head around this. So of the list of numbers 1,1,2,3,3,............,3,3 Do you want the row of the LAST largest number? All in one column? This does it for column A cells A1:A11: =MAX(IF(A1:A11=LARGE(A1:A11,1),ROW(1:11),0))...

Excel: Excel array sorting question, array formula, curly brackets
array formula, curly brackets, column c: Jason, You need an array formula to do this: =INDEX($C$1:$C$10,MATCH(LARGE($B$1:$B$10+ROW(1:10)/10000,1),$B$1:$B$10+ROW(1:10)/10000,0),1) entered with Ctrl+Shift+enter rather than just enter (since this is an array formula). After entering...

Excel: Excel Calculations, weekends and holidays, analysis toolpak
weekends and holidays, analysis toolpak, holiday dates: You need the WORKDAY function to do what you need - this is an add-in but a standard one - tools, add-ins, analysis toolpak - you THEN get this function which will add a given number of WORKING days to a date - it automatically discounts weekends, and you...

Excel: Excel Charts and formulas, excel, chart
excel, chart: If you have formulas, you may tweak them to show the #N/A error instead of a blank, as that will prevent those data points to appear on the chart at all. The formula you can use to generate the errors is NA(), and it can work something like: =IF(MONTH(TODAY())...

Excel: Excel Count Individuals not visits, excel count, time spans
excel count, time spans, pivot table: Simon, You ll notice that when you make a pivot table and you put the NAME in the row area, it gives you each name only ONCE, even if the name exists more than once in the data table. If you really make a pivot table that is limited to various time...

Excel: Excel - calculating time values which cross midnight & deducting breaks, work overtime, conditional tests
work overtime, conditional tests, pieces of the puzzle: Marmot, In cases like this, where there are lots of different possibilities to detect (did they work overtime, is there a break due, did they work past midnight, etc) , I find it best to add a bunch of columns off to the right that compute the various...

Excel: Excel - countif using ranges, tally chart, three colours
tally chart, three colours, score range: Sarah, You re looking for a multiple criteria record-summer/counter. Luckily there are several ways to do this! I can t believe you would even consider doing this by hand when there are all these spiffy neat functions just lying around, ready to...

Excel: Excel Data Swap, aidan, cells
aidan, cells, heritage: Not too clear how the swap is to take place - one option COULD be to select the block of cells you want to reorder and use a SORT to re-sort them independently of the rest of the data. Alternatively, a macro could be used to re-order - but the re-ordering...

Excel: Excel Dates, free microsoft excel, format cells
free microsoft excel, format cells, excel tips tricks: Select the column. Right-click on it. Select Format Cells. Change the date format accordingly. 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...

Excel: Excel, Flashing Colors on a conditional statement, sheet tab, conditional statement
sheet tab, conditional statement, rudimentary knowledge: Let s suppose the cell in question is D4. Right-click the sheet tab, select View code, put this in: Private Sub Worksheet_Calculate() DoInModl End Sub then Insert/Module, and put this in: Sub DoInModl() With Range( D4 ) If .Value ...

Excel: Excel Formatting, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, data validation: I would recommend using DATA VALIDATION and specifying TEXT as the allowed values. 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: Excel Formula, false answers, excel formula
false answers, excel formula, question thanks: I had assumed you had a list of values in another area - that would be the way to go - lets say 1 to 20 are in B1:B20 =countif($B$1:$B$20,A1) would give you the result. I GUESS it would be possible to do something without the values being explicit on...

Excel: Excel Formula, excel, references
excel, references: You may remove the $ sign from the 2 on the $E$2 reference, to make the row relative. This should make the formula easier to copy along the column, and it should return the proper values after any sort. =DATEDIF($E2, TODAY(), Y ) Hope this helps, Migue...

Excel: Excel Formula - IF and/or OFFSET??, excel, indirect
excel, indirect, offset: You can use the INDIRECT formula for that. For example: =OFFSET(INDIRECT(C10),8,0) This will return the value 8 rows below the cell referenced on cell C10. If you want the value 8 columns to the left, you can use a formula like: =OFFSET(INDIRECT(C10),0,-8)...

Excel: Excel Formulas, excel formulas, text string
excel formulas, text string, parenthesis: try =VALUE(RIGHT(LEFT(A1,SEARCH( ) ,A1)-1),LEN(LEFT(A1,SEARCH( ) ,A1)-1))-SEARCH( ( ,LEFT(A1,SEARCH( ) ,A1)-1)))) where A1 is the cell you are searching - I included VALUE to turn the text string into a number, though this part of the nest isn t strictly...

Excel: Excel Formulas, excel
excel: David, #1. in C10, you would put: =if(C7 0,if(c8 0,c9-(c9*5%),c9),c9) It does not make sense that c9 is the total of c9+c8, that would be a circular reference, so the formula above says, if c7 0 and c8 0 then in cell c10 show c9 minus 5%, otherwise just...

Excel: Excel Functions, g3 g4, dollar signs
g3 g4, dollar signs, excel functions: You cannot easily have a formula automatically down all 65,536 cells - and you probably wouldn t want it - but the formula =SUM($D$3:D4) put into G4 is a formula that can be dragged down to do the first part - the mixed reference here (use of dollar signs)...

Excel: Getting Excel to find items with a particular symbol or word and then paste it in a different column, FIND
FIND: Hi Julia, The FIND function can be used to identify characters in a string. Example: if cell A1 contained abc@yahoo.com, the following formula could test for the @ sign: =FIND( @ ,A1) The result would be 4, meaning it found the @ as the 4th character...

Excel: Excel formula doubt, excel formula, open tools
excel formula, open tools, referenc: Hi Ragin, Open Tools, options and click the calculation tab. Set iterations to one and check the iterate checkbox. Note: This setting is saved with your file BUT! it is set by the first file you open in Excel, so you may have to close down Excel before...

Excel: Excel formula...need help, logical tests, base details
logical tests, base details, sumif: I d be inclined to use a pivot table to do the data analysis, which would make the job automatic. Otherwise, it sounds like a multipe condition SUMIF function - which can be done with the sumproduct function =sumproduct(--(Range1=Test1),--(range2=test2),--(range3=test3),range4)...

Excel: Excel formula/ UDF, excel formula, udf
excel formula, udf, help menu: Hi Rishi How are you? I do not know any function for that. You may want to have a look at the list of functions on the help menu But I can suggest the following 1. Type the 5 values in A4:A8 2. Type in A1 the desired value 3. Type in B4 =abs(A8-A1)...

Excel: Excel formulas, sum formula, variable amounts
sum formula, variable amounts, d24: Johan, Something like this MIGHT be possible, but not the clever way you have tried. Whether it is possible or not depends on what type of changes you want to make to these formulas. If you want your formulas to simply operate on different...

Excel: Excel - If than functions, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, 599cd: Eric, sorry for taking so long to get back to you. I loved your question so much, however, that I made a free video tutorial out of it for my TIPS AND TRICKS section on my web site. Here s the link: http://599cd.com/tips/excel/complex-if-example?key=AllExperts...

Excel: Excel Help, bump in the road, excel help
bump in the road, excel help, aidan: as you ve specified to copy as picture, that s probably the problem - try Worksheets( 1722 ).Range( AV6:BT13 ).Copy instead - this will be the same as a copy via normal copy and paste - although I don t know the reason for this macro happening of course...

Excel: Excel help, target address, keyboard shortcut
target address, keyboard shortcut, false application: Assuming N1 is the activecell, then use the macro you put in your original post and make the change I suggested. This will NOT enter a formula and will enter the current date/time in that cell. I can t see your file so I have to assume N1 is the activecell...

Excel: Excel lost my function!, free microsoft excel, sheet tabs
free microsoft excel, sheet tabs, excel tips tricks: Pat, I ve never encountered this problem before, but it sounds like Excel is missing an object library. If you right-click on any of your sheet tabs and go to VIEW CODE. Click on Tools References. For Excel 2007, for example, you should see the Microsoft...

Excel: Excel Macro, debug error, excel macro
debug error, excel macro, wks: I ran the code on my own laptop before pasting it here, and it ran for me with no problem. At the moment, I m on a laptop with Excel2003 only. It s possible that Excel2007 demands a new way of dealing with borders. If so, I m sorry I can t test that for...

Excel: Excel Macro, false application, option buttons
false application, option buttons, word selection: the word selection has no meaning on it s own, so it s interpreted as an undeclared variable, which as it s undeclared has no value so will never be equal to 1 within this subroutine. I would PERSONALLY have the option buttons given the same groupname to...

Excel: Excel Macro, cell a2, 6 cells
cell a2, 6 cells, excel macro: Then use the same method ActiveCell.Offset(0, 1).Select.Cut ActiveCell.Offset(x, y).Select ActiveSheet.Paste Then put all of this in a loop like: For X = 1 to 10 ActiveCell.Offset(0, X).Select.Cut ActiveCell.Offset(x+1, 0).Select ActiveSheet.Paste...

Excel: Excel Macro Error Handling, blank cells, daily basis
blank cells, daily basis, code selection: In the code below, the IF structure determines whether there are any blank cells within the Selection, and proceeds with selecting blank cells only after confirming that they exist: If Application.CountA(Selection) Selection.Cells.Count Then ...

Excel: Excel Macro help needed, excel macro, sure what this means
excel macro, sure what this means, control button: Eduardo, It was a typo on my part. the cInt(date) cause the overflow because the date serial is larger than the max integer. Here is the modification (which I tested and worked for me). Private Sub CommandButton1_Click() Dim res As Variant res =...

Excel: Excel Macro Question, excel macro, open application
excel macro, open application, msgbox: How do you know that the macro is not running when you open the file, assuming you open it BEFORE 9:45? Nothing will happen UNTIL 9:45 if you open it BEFORE 9:45. Try putting a msgbox as the first line of the code to see if that appears when you open the...

Excel: Excel Matching, ascii character, logical formula
ascii character, logical formula, excel help: Hi Fernando How are you? Please try the following 1. Type 1 in A1 2. Type in B1 = Product &CARÁCT(A1+64) The problem is that I do not know the name in English for the function Caract (A1+64). Please look up the Excel help menu for a function that...

Excel: Excel macro, array formula, jump height
array formula, jump height, excel macro: Steve, I would just reserve some cells at the top of the sheet where the user would enter the name, and then you could find the row of that persone using the match worksheet function. Then you could use the information from Match to do your calculations....

Excel: Excel - Numbers custom format, comma, custom format
comma, custom format: Ofer, In your example, wouldn t you want the number to be 1,234 instead of 12,34? It s the position of the comma that is different. Well, one way would be to divide your original number by 1,000 and use the result to be the display number. of...

Excel: Excel Protection, check boxes, excel worksheet
check boxes, excel worksheet, color font: Jessica, In Excel 2003 & 2007, when you protect a sheet, a menu appears which lists all the things you want to allow or disallow the users to do. There are check boxes for all kinds of things, so you should be able to allow the users to do (or not-do)...

Excel: Excel pivotting in vba, range statements, case range
range statements, case range, best guess: Joe, I don t see any reason to loop: Dim ap as Range set ap = Range( AP5:AP9,BB5:BB9,BN5:BN9,BZ5:BZ9,CL5:CL9,CX5:CX9 ) ap.Numberformat = 0.00% Of if you want to loop set ap = Range( AP5:AP9,BB5:BB9,BN5:BN9,BZ5:BZ9,CL5:CL9,CX5:CX9 ) for each...

Excel: Excel Question, insurance inventory, cell reference
insurance inventory, cell reference, farmers: Not sure what the source for the drop down lists are - I d have it as a named range, and to the side of this list of categories I d use a SUMIF formula =sumif(ColumnA,Category,ColumnE) Where ColumnA and ColumnE are the ranges for the category and value...

Excel: Excel Question, excel, lookup
excel, lookup: Does it really have to be a VLOOKUP? You can achieve the same result on a simpler way with a formula like: =AVERAGE(OFFSET(Sheet2!A1,MATCH(A1,Sheet2!A2:A100,0),1,1,4)) In fact, with this formula you can include new columns on the average just by changing...

Excel: Excel: Re-organise table data into list, array formulas, clever method
array formulas, clever method, input sheet: Dave, Would you mind sharing your workbook with me? I d like to see the results. If you will, you can send it to ExpertAtExcel@AOL.COM. Can you explain what you did to fix the circular reference? SUPER-CONGRATULATIONS on getting such...

Excel: Excel Splitting Rows, databses, custom filter
databses, custom filter, new location: A macro would be OK to do it, but you d need to know how to write one - the SIMPLEST route would be to use a blank column and enter =Mod(row(),3) Drag this down the entire data range (easiest way here - ctrl shift end, followed by Ctrl D to fill the...

Excel: Excel Stumper, stumper question, data validation
stumper question, data validation, diamter: Gwen, Thanks for your reply. So here are the steps you need to take to make this work. 1) Combine all the separate tables into one table. They all have the same structure so this is just a copy & paste. We ll call the resulting table the DATABASE....

Excel: Excel SUM shortcut on mouse, free microsoft excel, sum command
free microsoft excel, sum command, excel tips tricks: You would need a programmable mouse or special software for this. I don t think it s possible with just a standard mouse and Excel. 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: Excel sorting copy and pasting to another sheet, column headings, pivot table
column headings, pivot table, filter option: Glenn You can use the Data Filter option, select the code you want and it will show only the ones you need, you can then copy and paste the cells to another table. You could also use a pivot table in the other tables but you would have to create it for...

Excel: Excel spread sheet, free microsoft excel, excel spread sheet
free microsoft excel, excel spread sheet, excel tips tricks: Not without a macro or some VBA code. Not that I know of, at least. 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: Excel IF statement for dates, conditional formatting, sop
conditional formatting, sop, excel if statement: Sherry, Your solution is to make a master summary sheet which brings in a copy of each persons summary. Then you can look at that sheet and see who needs training or not. If you already have the summary equations on each sheet, it should be easy to...

Excel: Excel to sum roll that match key word in col, allexperts, frontpage tips
allexperts, frontpage tips, spread sheet: Use the SUMIF function. Assuming you have 20 rows of data: =SUMIF(A1:A20, *repair* ,B1:B20) Enjoy. Let me know if you have any other questions or comments. Also, be sure to visit my FREE FrontPage Tips & Tricks Page at http://www.599cd.com/tips/FrontPage?key=AllExperts...

Excel: Excel Variable Use/Formula Reference in Cell, excel function, input cell
excel function, input cell, sums: Dan, There is an Excel function that does EXACTLY this! Its called SUMIF(). SUMIF uses a criteria to select which rows to sum. Normally, SUMIF sums the same column as the criteria is in..But, SUMIF has an extended mode that lets you specify...

Excel: Excel VB, excel vb, a1500
excel vb, a1500, test sheets: Hi Laura How are you? What about the following: Sub test() Dim FirstP As Range, LastP As Range, WaRange As Range Dim c As Range, Addr As String Rem Sheets( Dump WA_GSIL ).Select Set WaRange = Range( A1:A1500 ) For Each c In WaRange If c =...

Excel: Excel - VB/ Macros Help for Template, excel vb, template wizard
excel vb, template wizard, future dreams: OK, now I m thoroughly confused. I think it s time to send me the workbook/template. I don t know what you mean by Customer Name (I11,M11) and INV ### -- pretty confusing. I m hoping I ll get a better idea of what you want by seeing your workbook. Please...

Excel: Excel VBA, copy rows by date, string s2, excel vba
string s2, excel vba, datevalue: Marshall, the code you put in your post is identical to the code I sent you. Anyway, this has the ability to define 6 workbook names and the top row where to paste them in Down Time details. You and also specify the folder (I guess that is what you...

Excel: Excel VBA to search across multiple sheets, value case, word data
value case, word data, searchfor: Private Sub cmdSearchAcct_Click() Dim shtData As Worksheet Dim rngSearch As Range for each shtData in worksheets if instr(1,shtData.name, data ,vbTextcompare) then Set rngSearch = shtData.Range( A:A ) lisSearchresults.Clear...

Excel: Excel VBA search text in 3 columns, silicon controlled rectifier, input nand gate
silicon controlled rectifier, input nand gate, transistor bc547: Patricia, this code does what you want. since you don t need intermediate sheets, it copies results to Sheet2 Lightly tested, but it seemed to work well for me. Private Sub CommandButton1_Click() Dim FoundCount As Double Dim rFoundB As Range,...

Excel: Excel VBA write text based on another cell, cell c1, cell d1
cell c1, cell d1, cell b1: Daniel The formulas should look like this In cell A1 the formula would be =IF(B1=5, past due , ) This formula says if cell B1 is equal to 5 then put Past due in Cell A1, if it is not equal to 5 then put nothing in the cell. I am not sure if you...

Excel: Excel Visual Basic, boolean
boolean: Sorry -- try this: Private Sub Workbook_BeforePrint(Cancel As Boolean) If Range( H48 ).Value Yes and Range( H48 ).Value No then Cancel=True Msgbox You must enter Yes or No in this cell before printing. ,vbexclamation Range(...

Excel: Excel value changing, free microsoft excel, excel tips tricks
free microsoft excel, excel tips tricks, 599cd: You can use the IF function to have Excel place a value in a cell based on one or more conditions or values in other cells. See this FREE tutorial on my web site for more information on how to use the IF function: http://www.599cd.com/tips/excel/misc/if-function-sales-tax.asp?key=AllExperts...

Excel: Excel - WORKDAY function to add partial days, excel, dates
excel, dates, decimal: You could combine the SUM and the WORKDAY, using the former for the decimals and later for the days. Something like: =WORKDAY(A1,B1)+MOD(B1,1)+MOD(A1,1) This way, if you use the result of the formula as the starting day for the next task, it will keep the...

Excel: Excel Worksheet w tables, excel worksheet, a1
excel worksheet, a1, worksheet function: from the description it sounds as though the indirect worksheet function would be what you need if the dropdown is in A1 and returns A then =indirect(a1 & !a1 ) would give the data at a1 on sheet A - but if a1 is changed to return B, this would THen...

Excel: Excel workbook with links crashing, www google co uk, google co uk
www google co uk, google co uk, macro security: I can see that this is an issue faced by others (http://www.google.co.uk/search?sourceid=navclient&ie=UTF-8&rlz=1T4RNWN_enGB318GB318&q=excel+2007+spreadsheet+with+links+crashes ) BUT cannot find any resolution to it- it s quite a lot of links, but it SHOULD...

Excel: Excell Times, military time, time format
military time, time format, right time: Put this formula in a cell, assuming A1 has the dates you mentioned =Left(Trim(A1),Len(Trim(A1))-1) This should strip the a or p from the end. Assuming you put this in B1, then in C1, for example, simply use =Timeserial(B1) UNTESTED ...

Excel: Excell Times, number fraction, cell a1
number fraction, cell a1, military time: Andres: Assuming the file actually has the items entered as time in Excel (and not as text), then you should be able to do this: 1) Right-click on the cell (or entire column) and choose format. Select Time on the number tab. Choose one of the military...

Excel: Extend Range of Macro Calculation, question thanks, lns
question thanks, lns, f3: ...Whenever a change any cell in the range specified x, the macro runs through all rows and calculations... That is what is supposed to do. It makes no determination of what the data MIGHT have been or WAS BEFORE you run the macro - it just evaluates...

Excel: External References in Excel - Reg, external references, excel sheet
external references, excel sheet, good answer: Krishnamoorthy I don t work with networked drives so I don t have much expertise in that area. I suggest when you do your copy that you try a copy/paste special/formulas and see if that works. I have tried to duplicate your situation and I can t without...

Excel: Extract text based on return carriage, carriage return character, text cells
carriage return character, text cells, ascii code: JJ, I don t quite see how you can have the data you describe, because when you enter a RETURN into a cell it terminates the data entry into that cell. Nevertheless, this is what you say you have! But I d suggest maybe you have ALT-ENTER as the...

Excel: Extract text from a cell, cell a2, numerical characters
cell a2, numerical characters, alphanumeric text: Abhijit, The way to do this is to try to FIND() each of the 10 numerical characters, and then take the MIN() of the 10 find results. Some FIND() results might come back #NA so you need to change those to a number larger than the text string LEN when...

Excel: Extract value from R9C15 from multiple sheets, 3d range, vlookup formula
3d range, vlookup formula, cell values: Marion, You re in luck! There is a way to create a 3D range through all your worksheets! Once you have that, you can handily get to the cell values. The help file has a great page on how to create, manipulate & use 3D ranges. Search for...

Excel: Extract values form one sheet and display in another sheet, cell a2, vlookup formula
cell a2, vlookup formula, excel formula: Geeta If I understand your sheet setup correctly, I believe you can use the VLOOKUP formula to find the matching id numbers and bring over the ratings. The formula might look like this. =IF(ISERROR(VLOOKUP(A2,Sheet1!A$2:I$90,9,FALSE)), ,VLOOKUP(A2,Sheet1!A$2:I$90,9,FALSE))...

Excel: Extracting Rows, blank file, autofilter
blank file, autofilter, ogilvy: in file B, next to the 1000 entries put in a formula like for example, in B2 of file2 =if(countif([File1.xls]Sheet1!A:A,A2) 0, Copy , No ) then drag fill that down the 1000 rows Now select column B (or the column with the formula) and do Data=...

Excel: Extracting Rows, excel, filter
excel, filter, copy: You could the MATCH formula and the autofilter to create the list, and then copy/paste the results. The formula can be something like this on file B: =NOT(ISNA(MATCH(A2,[Book1]Sheet1!$A$2:$A$101,0))) And then filter (menu Data- Filter- Autofilter) for the...

Excel: Extracting Rows, zeros and ones, blank file
zeros and ones, blank file: I d do this with a formula in a helper column =countif(FileARange,FileBcell) where FileARange is the 100 entries from file a, and FileBCell is the cell you are checking in the 1000 entries - drag this down, you will then get zeros and ones (IF duplicate...

Excel: Extracting Valid Data from an Excel Column, array formula, curly brackets
array formula, curly brackets, 6 cells: Alastair, You can do it, but it will only work if both workbooks are open (since you say you will be linking to several workbooks,then all the workbooks would need to be open). I don t think that is what you want, so I would suggest putting a sheet in...

Excel: editing data, trunc, 1234567
trunc, 1234567, number 3: John, Sounds crazy but here goes! Use a series of steps (which may require more than one cell). 1) divide the nuimber by 100,000. 2) TRUNC() number 1) so only the integer part remains. 3) subtract number 2) from number 1) to get...

Excel: excel 2003-data formatting, weather down, bob bob
weather down, bob bob, tab characters: Bob, I d suggest that you save the Excel file as a TXT type (using SAVE-AS). This will give you a tab-delimited file (I think). Then you can import this (or open it) with WORD, and use the Find & Replace (Special) to replace all the tab characters...

Excel: excel column header pointer, excel, references
excel, references, move: In order to move cells, you can either copy/paste/delete original or cut/paste. Both have different effects: - Copy will adjust the relative references on the formula (without dollar signs) to the new position. Formulas that referred to the original cells...

Excel: excel copy and paste, excel, vba
excel, vba: Even with VBA the PasteSpecial feature doesn t work in the way that you need. You could try with something like: Sub CopyPositives() Dim rngOrigin As Range, rngDest As Range Set rngOrigin = Sheets( Mapa3str ).Range( A3:ES530 ) Set...

Excel: excel two criteria sumif dispite reading help cant get it to work, array formulas, result table
array formulas, result table, value customer: Brett, There is certainly a way (or two) to do what you want, but before I write a big explanation I need to know a few additional things. 1) Do you know beforehand what the products for each customer are, or is that something you need to find...

Excel: excel date calculation, date function, workday
date function, workday, step 6: Kristine, It may not be simple, but I have thought of a way. I m developing this live as I type, and my method is to use a column for each step. At the end, you can combine the steps together if you wish. I usually actually create the steps in...

Excel: excel dates, format cells, decimal number
format cells, decimal number, whole number: 032609 is a decimal number, which to excel means you have typed a whole number - which it is happy to display as a date - 32609 is the serial number for 04/11/89 - you NEED to enter dates as dates, so March 26 is fine, 03/26 is fine but 032609 will get interpreted...

Excel: Having excel determine which weights to assign to each category to match desired percentages, data cells, hello bill
data cells, hello bill, circular references: Sheldon, I don t think this can be done without a macro. I tried several crafty and clever solutions, all of which involved setting the calculation method to MANUAL and the number of iterations to 1. This was necessary because it seems inevitable...

Excel: excel file, zeros, a9
zeros, a9, berth: Hi Berth How are you? That is an interesting question: it seems easy but it is not. I have btried the following 1. Suppose your data is in A1:A9 2. in B1 type =small(A1:A9, countif(A1:A9, =0 )+1) This works if there is no number smaller that zero....

Excel: excel find in array, excel, match
excel, match, array: You can use an array formula similar to this: =INDEX(L1:L5,MATCH(1,(A1:A5=N1)+(B1:B5=N1)+(C1:C5=N1)+(D1:D5=N1)+(E1:E5=N1)+(F1:F5=N1)+(G1:G5=N1)+(H1:H5=N1)+(I1:I5=N1)+(J1:J5=N1)+(K1:K5=N1),0)) Enter it with Ctrl+Shift+Enter. In the formula I am supposing...

Excel: excel formula, excel, date
excel, date, difference: You can treat dates as numbers and do the same operations that you would do between them. For example, in this case you could do something like: =A2-A1-7 Supposing A1 and A2 contain the dates. You may probably need to format the resulting cell as number...

Excel: excel formula, blank cell, cell references
blank cell, cell references, excel formula: It s certainly possible - it sounds like it is actually just a case of adding the two values up - a blank cell evaluates to zero - so it would be something along the lines of =BalanceCell-Receivable1-Receivable2 where the balanceCell, Receivable1 and...

Excel: excel formula, cell b1, cell a1
cell b1, cell a1, bj2: I ll interpret your question thusly. You ve got data that starts in cell A1 and continues down col A. You want to count the # of times that there s a cell with the value BJ2.1-BJ3.1 STM13 AND a cell immediately below it with the value CIRCUIT in...

Excel: excel formula, bj2, conditional test
bj2, conditional test, blank cell: Solay, The easiest way to do this is with a column of IF statements which check for the conditions you specify... if the conditions are met, increment the number one row above me , otherwise put in a -1 (minus 1). Furthermore, if the number above...

Excel: excel formula, excel, sumproduct
excel, sumproduct: Solay, To handle this task, you can use the sumproduct function: =SUMPRODUCT(--(A1:A13= GL2-RR2 STM10 ),--(A2:A14= CIRCUIT: )) Adjust the A1:A13 and A2:A14 rages to match your range. Joseph Harris http://articles.excelyogi.com/cool-products...

Excel: excel formula, cell b1, cell a1
cell b1, cell a1, rr2: I ll interpret your question thusly. You ve got data that starts in cell A1 and continues down col A. You want to count the # of times that there s a cell that begins with the string GL2-RR2 STM12 AND a cell immediately below it with the value CIRCUIT...

Excel: excel formula, greek letter sigma, free microsoft excel
greek letter sigma, free microsoft excel, sigma press: To do this, use an Aggregate Query... also called a Summary Query or Totals Query. There is a little button on the query toolbar that looks like a wierd E (actually the Greek letter Sigma). Press that and you get a new Total row. Just add the fields you...

Excel: excel formula or Macro?, vlookup function, leftmost column
vlookup function, leftmost column, excel formula: Peggy, is it possible Yes since the source data has employeed name on the leftmost column of the data you would use the vlookup function It is explained very well in help - so start there. Here are much more extensive details on vlookup and the...

Excel: excel help, lip shape, best bet
lip shape, best bet, application work: Collins, Excel may not be your best bet to make your application work. The animation you describe (having one picture appear, and then be replaced by a second picture) MIGHT be possibly done with a macro; however, I don t do macros so I can t...

Excel: excel ranking, RANK
RANK: Hi Troy, RANK can go both ways, depending on how you set the third argument. =RANK(number,ref,order) If order is omitted (or set to zero or FALSE), then you get the default ranking in descending order. Change the order value to 1 (or TRUE or any...

Excel: getting excel sheet names, sheet names, myname
sheet names, myname, excel sheet: With a formula in each sheet: =MID(CELL( filename ),FIND( ] ,CELL( filename ))+1,255) With VBA Function: Function MyName() as String MyName=Application.Caller.Parent.Name End Function then use =MyName() in the sheet With VBA Function available...

Excel: excel tabs, category tab, tab section
category tab, tab section, info thanks: It COULD be done - it would need VBA to achieve it though, so would ONLY work if the users enabled macros the macro (one per master worksheet) would be along these lines Private Sub Worksheet_Activate() Sheets( Sheet2 ).Visible = False Sheets(...

Excel: excel Vba, runtime error 91, h2 h3
runtime error 91, h2 h3, excel vba: Randy, I missed your declaration Dim housecode1 As Range but the result of an inputbox is a string - so I had assumed you declared it string, but guess I didn t see you hadn t. So the solution to that is to declare it as string. The previously mentioned...

Excel: excel vlookup, core platform, f78
core platform, f78, doing the right thing: I m not sure I follow (I don t know what DealAnnualVolume is -- if it s 7, it s doing the right thing). It looks up the value in the FIRST column (A70:A78), and for a 7 it is on the 3rd row. If you want it to be the row before, what would happen if you looked...

Excel: execel, bottom right, answer the question
bottom right, answer the question: Not sure what you are asking - the formula =a1+b1 would add two values together. clicking on one cell and holding down ctrl then clicking on another cell should show the SUM in the bottom right of the screen copying one cell, selecting another...

Excel: how to expire Excel file after certain date, hardeep, john doe
hardeep, john doe, excel sheet: It will need macros - I d be inclined to have the master sheet set to VERY HIDDEN - so only if the macros are enabled would you be able to do anything - Sheets( Sheet1 ).Visible = xlSheetVeryHidden sets a given sheet to VERY hidden Sheets( Sheet1...

Excel: extract rows based on a number of values, copy worksheets, q cell
copy worksheets, q cell, phase number: Paul, If the other workbook is open in the same instance of excel or you want to have the macro open it. Assume it is open and it is named Master.xls with data on sheet1 Sub copyData() Dim r As Range, cell As Range, r2 As Range With Workbooks( Master.xls...

Excel: extracting name or word from a cell, array formula, curly brackets
array formula, curly brackets, ogilvy: Yu, In B1 put in this formula =MID(A1,MATCH(TRUE,ISERROR(1*MID(A1,ROW(INDIRECT( 1: &LEN(A1))),1)),0),255) It must be entered/committed with Ctrl+shift+enter rather than just enter since it is an array formula. If you enter it properly, if you reselect...

Excel: Filter and Copy Macro, rcell, copy columns
rcell, copy columns, filter records: jeff, This was tested and worked for me: (designed my workbook to match what your code shows it is looking for or you described). If your workbook isn t laid out that way, then your mileage may vary. Sub Recall_BT2() Dim rr As Range, r1 As Range...

Excel: Filter Data on basis of Dates, excel advanced filter, filter data
excel advanced filter, filter data, question thanks: Well, my answer is still the same. You get FALSE because there are no dates between the dates you have typed. As far as a macro is concerned, then sorry, I don t write macros for people on this site. Please see my introduction. Try this link to learn...

Excel: Filter Macro in Excel '07, recruitment methods, macro in excel
recruitment methods, macro in excel, external candidate: Obviously I don t know the data structure, but to me it SOUNDS as though the query to Access should be the key- this could be modified to bring in ONLY those employees in the appropriate group - so I guess the way you are querying it needs modification (which...

Excel: Filter of my own, proprietary technique, database terminology
proprietary technique, database terminology, course reference: Dario, There is a way to make this happen but its VERY complex. I can outline it for you in a top level way but the actual equations will all depend on where the various cells are. 0) The general concept is to mark the rows (also called records...

Excel: Filtering questions answered with a yes or no in another spreadsheet (without using Autofilter), free microsoft excel, mexico cuba
free microsoft excel, mexico cuba, excel tips tricks: I couldn t explain quickly and easily how to have them all directly one after the other, but you could do something like this: =IF(Sheet1!B1= yes , USA , ) This would make the current cell say USA if Sheet1!B1 had a yes in it. You could do this...

Excel: Filtering records in Excel, cell a2, column c
cell a2, column c, home tab: Bianca, I will assume the employee always appears first in the data. Assume the 88 Employee appears in row 2 and the 88 is in cell A2 in the next available column (for illustration, I will use column C), so in C2 put in this formula =if(countif($A$2:A2,A2)...

Excel: Finding a 10 digit number within a text field, digit number, evelyn
digit number, evelyn, sequences: I m sorry but I m STIL confused. If there are 2 ranges of numbers like in your example, do you want to see the smallest to largest of EACH range so in your example you d see 9855123450 9855123451 9855123452 9855123453 ... 9855123460 9855123461 9855123420...

Excel: Finding duplicates, duplicat, ogilvy
duplicat, ogilvy, occurance: Evelyn, in B1 or the next available column (or insert a new column B) put in a formula =if(countif(A:A,A1) 1, Duplicate , ) then drag fill down the column. Also, that doesn t require that you sort the data first. If you want to ignore the first...

Excel: Finding next value, value question, cell reference
value question, cell reference, question thanks: Error trapped version first =IF(OR(A2=1,B2=1,C2=1),MIN(IF(ISERROR(MATCH(1,D3:$D$1000,0)),9999,MATCH(1,D3:$D$1000,0)),IF(ISERROR(MATCH(1,E3:$E$1000,0)),9999,MATCH(1,E3:$E$1000,0)),IF(ISERROR(MATCH(1,F3:$F$1000,0)),9999,MATCH(1,F3:$F$1000,0))), ) now to...

Excel: Finding number of characters in a cell and..., ltrim, range c
ltrim, range c, excel sheet: Hi Sirajuddin How are you? Please do as following: 1. Run Visual Basic editor (it depends on the Excel version.( For Excel 2003, Tools-Macro- Visual Basic Editor) 2. Insert - Module 3. Copy Sub leadingzeros() Dim R As Range, c As Range Set R =...

Excel: Finding number of characters in a cell and..., excel sheet, vba
excel sheet, vba, len: As I said it was untested and I assumed you knew something about macros and could have fixed the syntax error. Secondly, ONLY saying that you are getting some error and then expect me to know only from that WHAT the error is is like you telling a doctor...

Excel: Finding number of characters in a cell and..., cell a1, data cells
cell a1, data cells, excel sheet: Say for example that you have your data starting in cell A1 and extending down col A. In cell B1, enter the formula: =IF(LEN(A1)=5, 00: ,IF(LEN(A1)=7, 0 , ))&A1 and copy it down col B. Col B will then display your correct results. If you re familiar...

Excel: Finding number of characters in a cell and..., excel sheet, ms office 2007
excel sheet, ms office 2007, len: Siraduddin, This can easily be done with a compound (nested) IF statement. If the text is in cell A20, then in cell B20 you would write: =IF(LEN(A20)=5, 00: &A20,if(LEN(A20)=7, 0 &A20,A20)) Your question didn t say so, but I ve assumed...

Excel: Finding number of characters in a cell and..., cell references, excel sheet
cell references, excel sheet, a1: Sirajuddin You can use this formula =IF(LEN(A1)=5, 00: &A1,+IF(LEN(A1)=7, 0 &A1, )) Where A1 is the cell that contains the time that you want to convert. Remember to format the cell that you put the formula in as text. You should be able to...

Excel: Find_Sum, science problem, science world
science problem, science world, jpg file: Binh, Excel does not have a function to do this. Its actually a terribly complicated computer science problem, especially if you don t know how many number are involved. There are just so many possibilities. The only way I could approach this is...

Excel: Follow up to Earlier question, blank row, false application
blank row, false application, info column: Assuming the following: Your data, in its original state, starts in cell A1. The year values in col A continue down the column without break; the first blank cell in col A marks the end of the data. You want to insert a blank row in each case where the...

Excel: Follow up from Excel Issue, google search, dynamic ranges
google search, dynamic ranges, google: Dan, I ll refer you to my original answer which discussed DYNAMIC RANGES. This is what [I think] you need to learn to allow your ranges to adjust to the amount of data they encompass. They work great but are tricky to learn. Read about the OFFSET...

Excel: Formatting cell X based upon cell Y, conditional formatting, format button
conditional formatting, format button, home tab: Mike Select cells C9 down to C - last row you want to format. Make sure C9 is the active cell as the formula we will use will be set up to be relative to the activeCell (C9). In xl2003 and earlier, in the menu, choose Format, then conditional formatting...

Excel: Formula, format button, spreadsheet
format button, spreadsheet: Select all the dates, use Format/Conditional Formatting, change Cell Value Is to Formula Is , enter this formula (this assumes D1 is the Active cell -- change accordingly: =AND(D1 =TODAY(),D1 =TODAY()+90) then Click the Format button, and the Font (or...

Excel: Formula to calculate additional rent % of sales after a guaranteed fixed sum is met, preset limit, e32
preset limit, e32, e4: Milton, Here is an answer for you. Its very simple and I am sorry it took so long. What you want is this: If E29 E4 take 20% of the difference, other wise E29 = E4 so put in a zero. Piece of cake. The formula you need is in cell G31....

Excel: Formula in Excel, formula x, whole number
formula x, whole number, two ways: Hi Kay, Two ways: 1. Round the number in the original location using the ROUND function In the cell with P use: =ROUND(ORIGINAL_FORMULA,0) the other formula stays the same 2. round the number in the new formula using the ROUND function: X*Y/ROUND(P,0...

Excel: Formula Help!, excel, if
excel, if: I am not sure on what is the layout of the formulas, but probably you could use the IF function: =IF(A1 50,Value1,IF(A1 100,Value2,IF(A1 200,Value3,Other))) If this doesn t work for you please let me know, and send more details on how the data is structured...

Excel: Formula Keeps giving Circular Reference, circular reference, quote detail
circular reference, quote detail, person group: A circular reference means that a formula is referring to itself, so getting into a potential continuous loop - but the formula itself doesn t give me any clues as to what you are trying to resolve - I don t know where the error will be - but if you would...

Excel: Formula or Macro in Excel 2007?, macro in excel, a6
macro in excel, a6, c6: in C2: =IF(COUNTIF($A$2:A2,A2) 3,1,0) and fill down. in C6 in this example (the first delete) you ll see =IF(COUNTIF($A$2:A6,A6) 3,1,0) and the COUNTIF part will be 3 so this formula will return 1. Before you manually sort you need to copy all these results...

Excel: Formula Question for MS Excel 2003, Using SUMIF, row numbers, search range
row numbers, search range, shipping department: Ian, What you need is to have those ranges become DYNAMIC so they adjust to the data automatically. This is done with the OFFSET function, which is used to specify a range depending on other cell values in the spreadsheet. In your examples and...

Excel: Formula for Time Keeping, format cells, time type
format cells, time type, a3: In cell A1, enter 9:50 AM In cell A2, enter 7:15 PM Select cell A3. From main menu, choose Format, Cells, Number, Category: Time, Type: 37:30:55, OK. In cell A3, enter =A2-A1 A3 now displays 10:10:00, meaning that between 9:05 AM and...

Excel: Formula for Time keeping, excel, cell
excel, cell, format: When that appears is probably a matter of cell format. Try first to increase the column width, so you can see if it is an error or it is a format that didn t fit. If it is the later, you can change the cell format to something that fits better. If it is...

Excel: Formula vlookup, good answer, colum
good answer, colum, many thanks: Jody Sorry to be late in answering, I must have missed your question. I am not sure if your cumulative amounts are cumulative for the month or cumulative for the year. The combination of conditions that you want to measure are difficult for me to write...

Excel: Formulas in workbook, 3d range, desire one
3d range, desire one, line numbers: Robert, If you desire ONE formula to read ALL your worksheets, it is POSSIBLE but that might be a big long complex and difficult formula. Do you really want to condense ALL your worksheets into ONE NUMBER??? That s all that a single formula can do....

Excel: Formulation table, code column, blank cell
code column, blank cell, material code: Sub test1() Dim rw as Long Application.Goto Reference:= RawMaterialDatabase Range( Banco_MP ).AdvancedFilter _ Action:=xlFilterCopy, CriteriaRange:=Range _ ( RMfilter ), CopyToRange:=Range( RMoutput ), Unique:=True Application.Goto...

Excel: Frequency of Dates, cell reference, day of the year
cell reference, day of the year, histogram: Brandon, Do you mean, if you have 10 years of data, you want to summarize all 10 May 1sts, all 10 May 2nds, all 10 May 3rds.... etc? I think what I would do is make a NEW column and create a new date from the original date, a date that would...

Excel: Function to count number of days in months between two dates, data inputs, excel 2002
data inputs, excel 2002, categorised: So, 01/01/2009,12 01/02/2009,20 01/03/2009,22 01/04/2009,13 means that there are 12 days in Jan, 20 days in Feb, 22 days in March and finally 13 days in April starting at 1/15/2009 and ending at 4/18/2009. If do then these numbers represent the...

Excel: add an excel graph in e-mail with VBA, archive index, forum archive
Excel: add an excel graph in e-mail with VBA, archive index, forum archive, spreadsheet

Excel: Conditional formatting, answers to the questions
Excel: Conditional formatting, answers to the questions

Excel: Converting the date to number of days, leap year, a1
Excel: Converting the date to number of days, leap year, a1, datedif

Excel: To Copy ranges from multiple workbooks to one sheet
Excel: To Copy ranges from multiple workbooks to one sheet

Excel: Count unique data in unfiltered rows, c26, a26
Excel: Count unique data in unfiltered rows, c26, a26, c1

Excel: Currency symbols, exact functionality, mortgage calculator
Excel: Currency symbols, exact functionality, mortgage calculator, xls

Excel: combining date & time from two columns, time type, time column
Excel: combining date & time from two columns, time type, time column, column type

Excel: compare two lists in excel, e mail addresses, excel conditional formatting
Excel: compare two lists in excel, e mail addresses, excel conditional formatting, e mail address

Excel: convert text to .xyz, cheers
Excel: convert text to .xyz, cheers

Excel: copying cell color, colorindex, different color
Excel: copying cell color, colorindex, different color, cells

Excel: How to disable editing while multiple sheets are selected?, target
Excel: How to disable editing while multiple sheets are selected?, target

Excel: Excel 2003 - Autocomplete, blank cell, blank row
Excel: Excel 2003 - Autocomplete, blank cell, blank row, column data

Excel: Excel Formula - IF and/or OFFSET??, layouts
Excel: Excel Formula - IF and/or OFFSET??, layouts

Excel: Excel IF statement for dates, new question, sherry
Excel: Excel IF statement for dates, new question, sherry

Excel: Extend Range of Macro Calculation, unprotect cells, going in the right direction
Excel: Extend Range of Macro Calculation, unprotect cells, going in the right direction, target

Excel: editing data, a1, len
Excel: editing data, a1, len

Excel: Hide/show cells for specific users, white on white
Excel: Hide/show cells for specific users, white on white

Excel: index match to find all values matching the criteria in 1 column
Excel: index match to find all values matching the criteria in 1 column

Excel: Number format in concatenate, guru
Excel: Number format in concatenate, guru