Sanddeep Since I don t know which version of the code you are using, I will modify both. Hopefully this will do what you want. Sub copydata() Dim WB1 As Workbook, WB2 As Workbook Dim sh1 As Worksheet, sh2 As Worksheet Dim sName As String Dim dataB...

Excel: Dates and Formulas

to get it as a number use =month(YourCell) and if you would prefer text then =text(yourcell, MMMM ) in both cases replace my YourCell phrase with a cell reference! Once you have that use autofilter to filter on any given month, or a pivot table to summarise...

Excel: Deleting text in green cells

Gail, You can sort and filter on color. So you could apply a filter to your data. Then in the dropdown, since you have colored cells, you can select filter by color. Select the green color and all but the rows with green cells in that column will...

Excel: Excel 2007 Windows Formula

Hi Rick, I apologize for taking so long to answer your question. I had to work on it pieces at a time complete it. Here is code that I believe does what you want. You didn t mention autofilling the formula in K2 down (Part 2), but I assumed you wanted...

Excel: Excel

Jim WSorry I have been away from my comuter for several days. I believe you can get to your results with a countifs formula, and using different criteria to count the societies that fit within your paramaters, something like this. =COUNTIFS(D$2:D$11,...

Excel: Fill data in SECOND wb from data of FIRST wb

Hi Sandeep, I ve created the below macro to look for one value in first sheet s first column and copy all the matching rows to the second sheet. This is a basic macro which will search the value entered in cell A1 of first sheet, it will search in the...

Excel: Fill second workbook with data of FIRST workbook

Hi again Sandeep, If you want the code to be automatically triggered by entering a new name in B2 enter this code into the worksheet s event code module: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $B$2 Then Application.EnableEvents...

Excel: Finding a value in a range and returning info from another column, VLOOKUP closest match

VLOOKUP closest match: Michael: VLOOKUP is the way to go; however, you need to tweak one of the parameters to search for the closest match (TRUE) instead of an exact match (FALSE). Assuming your data is in A1 to C6, you would use the following: =VLOOKUP(cellcontaining650,A1:C6,3,TRUE)...

Excel: formula help

Natalie, Chip Pearson has an extensive writeup on using VBA to assess colors. http://www.cpearson.com/Excel/colors.aspx It is quite involved and complete but I offer it for your consideration. It is also assumed that you manually go to the cell...

Excel: formula syntax

Christopher, Assuming your vlookup functions work, then you have left out several ampersand characters to concatenate the results together =VLOOKUP(Q2,COMMENTS,2,FALSE)&CHAR(10)&CHAR(10)VLOOKUP(R2,COMMENTS,2,FALSE)&CHAR(10)&CHAR(10)VLOOKUP(S2,COMMENTS,2,FALSE)&CHAR(10)&CHAR(10)...

Excel: inserting lines in large range of data

Gail, All macros must reside in a workbook. So you just need to make a workbook and put the macro in a general module in that workbook. A general module is when you go to the Visual Basic Editor (Alt+F11 from the workbook takes you to the VBE) and do Insert=...

Excel: Limit of 7 IF statements

You are correct that versions of excel from 2010 onwards allow more levels of nesting, but to benefit from these you have to be using one of those versions - the limitation is in the program, not the file format. That said, it is rare that I ve found the...

Excel: A follow up to my last question

Barry, Sub GetAllExcelHits() Dim sPathMas As String Dim sPathHits As String Dim r As Range, cell As Range sPathMas = E:AAA_SaveAA OTO DB Excel Master-All sPathHits = E:AAA_SaveHITS Set r = Range( A4 , Range( A4 ).End(xlDown)) If r.Count ...

Excel: Macro for value change

Jay, Right click on the sheet tab for these cells and choose view code. then paste in this code in the resulting module: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $D$2 Then Range( G2 ).ClearContents End If End...

Excel: Macro or VBA for picklist

Hi Keith, Apologies for delayed response. It would be a little difficult to explain the solution I had in mind in more detail than what I already explained above. Can you please drop me an email, we can exchange contact numbers and take it forward more...

Excel: Macros

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $D$6 or Target.Address = $D$7 then Range( B20 ).Value = Range( B15 ).Value Range( B74:B78 ).Copy Range( B51 ).PasteSpecial xlpastevalues end If End...

Excel: merging workbooks

Charlie, You show workbook1 and workbook2. Generally data is in a sheet in a workbook and to give a proper example, the formula would need to refer to the sheet. so assume Workbook1 is actually [Workbook1.xlsx]Sheet1 and Workbook2 is actually ...

Excel: Nesting cells

Excel 2003 can t save files as 2013...you need to have 2013 (actually, 2007 or 2010 will do as well - same file format). There s no way to increase the 7 nested if-statments in 2003, but depending on what you re testing for you can get away with 100 s of them,...

Excel: network problem

Sorry - the only thing you re asking that I understand is your circuilar reference. You had =SUM(I14:I15) in cell I15, and that s a pretty obvious circular reference, since you have I15 in the formula which sits in cell I15. Every other question is something...

Excel: oldest and latest dates for unique list

Yu, assume A1:A36 holds your letters and B1:B36 holds your dates D1: a D2: b D3: c D4: d D5: e E1: =MIN(IF($A$1:$A$36=$D1,$B$1:$B$36,100000)*1) entered with Ctrl+Shift+Enter rather than just enter since this is an array formula F1: =MAX(IF($A$1:$A$36=D1,$B$1:$B$36,-1)*1)...

Excel: Requesting a Macro Advise

Hi Jay, I m not able to understand your question. Can you be more specific with the question, what is the data you are entering, what validations you are using, clear description of the issue and probably a screenshot or link to the file will also help...

Excel: Requesting a Macro Assistance

Hi Jay, I assume when you refer to change a validation you mean change the value in the validated cell. To have Excel automatically delete the contents of G2 simply insert the following code in the worksheet s event code module: Private Sub Worksheet_Change(ByVal...

Excel: Response to your question

Barry Sorry to be late in answering, but I am a CPA and I have been and still am just a bit busy right now. I don t have the time to give you a complete answer but I would suggest you use the vlookup formula to do your search and bring back the data to...

Excel: Running Macro

Eric, That was my typo/omission. $D$6 is a string, so it must be in double quotes Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $D$6 then the code in this section will run only if a cell D4 was edited or you pasted...

Excel: Spreadsheets Required

IBTESAM It is possible to develop a system from excel but there are numerous systems already available that will do a much better job that we can develope without putting hours and hours into the system. I strongly suggest that you look into Quickbooks....

Excel: IF statement with "and "or"

Robert =if(OR($A$255=2,$A$255=3), Meets criteria , doesn t meet criteria ) =If(or($E$13= Yes ,$E$13= No ), Meets Criteria , doesn t meet criteria ) =If(or($D$23= Yes ,$D$23= No ), Meets Criteria , doesn t meet criteria ) If you wanted to check...

Excel: IF statement (to many arguments)

Robert, this worked for me. =IF(AND($A$255=2,$E$13= NO ,$D$23= NO ),( -T ),IF(AND($A$255=3,$E$13= NO ,$D$23= NO ),( -T ),IF(AND($A$255=2,$E$13= YES ,$D$23= NO ),( -TCC ),IF(AND($A$255=3,$E$13= YES ,$D$23= NO ),( -TCC ),IF(AND($A$255=2,$E$13= NO ,$D$23=...

Excel: VBA

Chris, your procedure used wksTarget, but never defines what sheet that is. I added a place in the routine where you need to do that. Also, it isn t clear what sheet needs to be active when your procedure is run. It seems to assume a specific sheet....

Excel: VBA MACRO PROBLEM

If DetailAddr is on the same sheet as the active sheet, then you probably need Range( DetailAddr ).Select You didn t specify HOW you set the original range DetailAddr. If you did Set DetailAddr = ActiveCell (or something like that), then the statement...

Excel: VBA solution to create a files structure

Alison, Microsoft documents three different methods for recursing through directories/folders with sample code that can be adapted to Excel. http://support.microsoft.com/kb/185476/EN-US/ How To Search Directories to Find or List Files http://support.microsoft.com/kb/185601/EN-US/...

Excel: Vlookup with more than one returned ouput

Michael, If you want to send me a sample workbook with some sample or real data I will show you how to do it using formulas. send to twogilvy@msn.com Another way to do it would be to use a pivot table if you know how to do pivot tables. --...

Excel: Average quantities within that date range for a specific name

Deb, you can use the AverageIFS function (note the S on the end). This was introduced in Excel 2007 so you will have access to it Here is the usage: AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2…) for your situation...

Excel: change font for printing a page header

Bill, The easiest way to get the syntax is to manually enter a header or footer and formatting it while the macro recorder is on. Then turn the macro recorder off and look at the recorded code. As an example: With Activesheet.pagesetup .CenterHeader...

Excel: Combine multiple tables/spreadsheets

Hi Gabriel, This can only be done easily using a macro. The code below assumes you have a worksheet called AllData and opens all files you select, copies all content of all sheets into that worksheet: Sub GetOpenFileNameExample3() Dim lCount...

Excel: Combine tables/spreadsheets

A question this complex would undoubtedly take weeks to cobble together if you have no working VBA basis to work from. I have an extensive basis and have performed this specific task 100s of times for clients, which is why it is a known and manageable thing...

Excel: Conditional Formatting

Hello Randy, Recommendation: Once you have completed the formula and are happy with the result, use the autofill function (or copy and paste the formula) to complete the list Sincerely hope this helps. Kind regards, Mary Lindsey LeLD...

Excel: conditional formatting syntax

Christopher Mitchell, If you are using a formula to determine whether to apply the formatting (this is done in the conditional formatting dialog), then you want to return a true or false answer. =vlookup($a$24,gradecon,2,false)=vlookup($a$25,gradecon,2,false)...

Excel: Conditional Lookup

Bob, This is written for data in rows 1 to 1000. Change the 1000 to a bigger number to check more rows. =IFERROR(INDEX($A$1:$A$1000,SUMPRODUCT(ROW($A$1:$A$1000),--($B$1:$B$1000=$G1),--($C$1:$C$1000 =$H1),--($D$1:$D$1000 =$H$1)),1),0) The formula...

Excel: Countif with Duplicate Removal

Bill, So you want to count how many unique identifiers there are for each company-week number combination. How about sending me a copy of your workbook and I will see if I can come up with a formula for you. Before I recommend something, I would like...

Excel: Data validation - Team Name, Thread and Sub-Thread

Bimmy, The most common approach is to name each range of cells that will be used in the dropdown So for team abc you would create a range of cells named abc that contained the threads for abc then say team is in D1, the definition for the list for...

Excel: Delete a range name

Hi Robert, I haven t used Excel 2010, having skipped from Excel 2007 directly to Excel 2013. However, I believe you can select multiple names from the list of all names in the Name Manager by holding down the Ctrl key as you select each name. Then you can...

Excel: Filtering from a macro using external cell

Bill, I assume there is only one Table on the sheet so I just use the index number of 1 in the ListObjects collection to set a reference to the table (listobject) Field:=1 applies the filter to the first column in the table. Change to reflect your...

Excel: Find Cell from ComboBox Value and write to adjacent cell

Bimmy, You can send the workbook to be at twogilvy@msn.com make sure you tell me exactly what you want to do. Also, this is a peer to peer support network. I provide technical advice and you implement it. Since you are having problems, I will...

Excel: Formula

Tony, This should do what you described: =INDEX($C$1:$C$10,MATCH(REPT( z ,255),$C$1:$C$10,1),1) If there are multiple text strings, then it will return the last one counting from row 1 to 10 If there are no text strings, it will return #N/A ...

Excel: Formula Help

You can use INDEX/MATCH to work like a VLOOKUP but for the columns being in any order. Again, this will only find the one value x and mark that row. It wouldn t find other rows marked with an x without having to resort to some heavy array calcs, which...

Excel: formula syntax

Chris, I have enclosed each VLOOKUP with Iferror(vlookup statement, ) =iferror(VLOOKUP(INDIRECT( M & inpRow),comments,4,FALSE), )&CHAR(10)&CHAR(10)&iferror(VLOOKUP(INDIRECT( N & inpRow),comments,4,FALSE), )&CHAR(10)&CHAR(10)&iferror(VLOOKUP(INDIRECT(...

Excel: formula syntax

Chris if you have a formula that produces a text string, then you can add this to the formula =Formula = - &Formula So if I understand what you want: = - &IFERROR(VLOOKUP(INDIRECT( M & inpRow),comments,4,FALSE), )&CHAR(10)&CHAR(10)&IFERROR(VLOOKUP(INDIRECT(...

Excel: Help with macro to clear data from rows & columns

Hi Keith, Hyperlinks are not treated like cell formulas so removing rows or columns will not update them. For the crew Shares there is a simple solution: use the hyperlink worksheet function. The one for Kenny Thomas would become: =HYPERLINK( Crew...

Excel: link back to data worksheet

the photos always come across as very hard to read (probably my age!!) but I think I follow the problem - the easiest, though most labour intensive, method would be to use hyperlinks. The alternative would be to use VBA to take an action if a cell is clicked...

Excel: Macro for CUT and PASTE VALUE

Thiago, to do a cut and paste, you select a range, cut, select another range and then paste. in code you can make the source variable, but you need to already know the destination. Selection.Cut Range( B9 ) As you already state, you can t pastespecial...

Excel: Macro to make userform appear when clicked anywhere in Column D

Bimmy, so that would be the selection change event. But that means if the user ever clicks in column D or navigates to it on the worksheet, the userform would popup. You need to be more specific in how this would operate. You can send the file to...

Excel: macros give incorrect answers

I can certainly try, post the code and I ll see if anything jumps out. But the problem may not be the code, it may be your workbook. 1) Are there merged cells in that range? 2) Are these cells manually colored or with conditional formatting. An...

Excel: Two macros I hope you could help me with.

Hi Barry How are you? The letter I is a variable and could not be replaced with 1 (one). I takes the value 1 and every time the loop runs gets the value I=I+1, i.e., I is invcremented by one immediately before Loop But ... have you written the...

Excel: multipage

Best is to set page breaks. Best to see where that would go is to click on page break preview and you can drag around the page breaks to fit where you want. If you can t do that for some reason, feel free to send me the workbook at bobumlas@hotmail.com. Use...

Excel: Multiple criteria lookup

Mike, I would suggest using a dummy column to evaluate at the row level. so for row 2 M2: =AND($L2=2,COUNTIF($F2:$J2,1) 0,COUNTIF($F2:$J2,5) 0,COUNTIF($F2:$J2,8) 0) then I would drag fill that down column M. Then you could do a simple =Countif(M:M,True)...

Excel: your reply

Moshe, Dates are stored as the number of days from a fixed point. In the windows version of excel this is 1900 (midnight on Dec 31, 1899). So if you want to just add or subtract days from a date, then you would just do =DateValue( May 12, 2015 )-2...

Excel: Shifting a range of cells

Denny, YOu don t say, but I guess you are asking for a macro. Sub ShiftRight() ActiveCell.Resize(5, Range( C1 ).Value).Insert Shift:=xlToRight End Sub should do what I understand you to describe. You have to run this macro. -- Regards, ...

Excel: Subtracting a fix number of days from a date, without weekends

I very nearly gave you the wrong answer, which would have been networkdays.intl - it is this that counts days between two dates. From the site I referenced The Excel Workday.Intl function returns a date that is a supplied number of working days (excluding...

Excel: subtraction fix amount of days

Using Excel 2010 or later, format col A as date. In cell A1, enter May 10, 2015 In cell A2, enter =WORKDAY.INTL(A1,-2,7) In the formula above, the first argument is the start date; the second argument means 2 days prior, and the third argument...

Excel: Time interval question

I think your last one in the list should be 5/19/2015 4:59, not 5/18. Assuming the first ON is in cell C2, then put these formulas in: D3, fill down to D11 (D2 blank): =IF(A3 A2,0,24*(B3-B2)) E3, fill down to E11 (E2 Blank): =IF(D3=0,0,E2+D3) F3,...

Excel: Userform not picking up dates

Instead of res = Application.Match(CLng(CDate(Me.cmbDate.Value)), r, 0) try something like Set r1 = r.Find(What:=cmbDate.value) If r1 is nothing then MsgBox cmbDate.Value & was not found Exit Sub else iRow = r1.Row End If ...

Excel: Thanks again. All is fine.

Barry, I suspected you had pasted it somewhere other than into the code module. I was thinking some type of non-printing character(s) was/were added. I don t think Font has an effect mainly because in the VBE, I could find no place where you could edit...

Excel: VBA writing

Christopher Mitchell, Test this on a copy of your sheet until you are sure it does what you want. Sub removedupes() Dim i As Long, rA As Range, rD As Range For i = 3000 To 2 Step -1 Set rA = Range( A1:A3000 ) Set rD = Range( D1:D3000 ) ...

Excel: 9x9 Grid

If you know the MAX number expected in any one category, then you can certainly expand that 9x9 grid solution to utilize the higher number of rows. The array formulas in each section are unique to each group, so inserting the number of needed rows in...

Excel: auto remove leading/trailing spaces

Not directly, and definitely not by formatting. You COULD use a helper column to have the TRIM value of the queried data in it. OR your formulas which reference it could potentially be adapted to handle the space issue. I d need slightly more information...

Excel: Copy datas from Sheet1 to Sheet2 Range Wise

Bimmy, Private Sub CommandButton1_Click() Dim ans As Long, i As Long Dim rw2 As Long Dim r As Range Dim sCol1 As String, sCol2 As String Dim s1 As Variant, s2 As Variant Dim sh1 As Worksheet Dim sh2 As Worksheet Set sh1 = Worksheets( Sheet1 )...

Excel: Copy datas from Sheet1 to Sheet2 - Row or Range Wise

Bimmy, This is what I understand you to want: Private Sub CommandButton1_Click() Dim ans As Long, i As Long Dim rw2 As Long Dim r As Range Dim sCol1 As String, sCol2 As String Dim s1 As Variant, s2 As Variant Dim sh1 As Worksheet Dim sh2 As Worksheet...

Excel: count of repeated unique numbers

two emails sent, but my server does strange things sometimes! OK, the original question you have answered yourself - my method would have been to suggest using a pivot table, which would give you the unique summary you wanted - but you have a method (though...

Excel: i don't have a clever response here!

Chris, Nice to meet you as well. In any cell you can put =Indirect(A18) then drag fill that to the right. that will pull in the value in cells e1, b1, c1, d1 and a1 Now select those five cells and do a copy then select A18 and do a paste...

Excel: excel 10, windows 7

Sorry to be late getting back to you, I must have inadvertantly deleted your question from my e-mail. I am not available currently to answer you question because of illness. Please reask your question of another allexperts person and I am sure you can...

Excel: Excel 2010

In some cases that would make a difference. However, I put years in B1:B whatever and I named that range Installed. In C1 I put in my formula and drag filled it down. It worked fine because even though Installed is a column range, since the formula is in...

Excel: but excel question using named references

Richard, So in D3 I had a dropdown built with data validation that refered to a named range of cells that contained entries like Weldline, upsetter, and so forth I had corresponding named ranges with these names and these named ranged contained anywhere...

Excel: formula help needed

Hi Siraj, I m not sure I am correctly interpreting your question. I believe you want the value that is in the cell that the hyperlink in L4 points to placed in N4. If this is what you want you can just use the formula =INDEX(A:A,K4) which returns...

Excel: formula syntax

Christopher Mitchell, You could put this in G2: =INDEX({1,2,3,4,5,6},MATCH(LARGE(COUNTIFS($A:$A,$F3,$D:$D,{1,2,3,4,5,6,7}),COLUMN(A1)),COUNTIFS($A:$A,$F3,$D:$D,{1,2,3,4,5,6,7}),0)) and drag fill down and then to the right. However, I have assumed...

Excel: index formulas 2

Hello Siraj, I ve gone through your excel sheet but I m not clear with your requirement. Can you be more precise as to what you need in this excel: 1. What are the input values 2. What is the logic or calculation or comparison required 3. What is the...

Excel: index formulation help

Hello Siraj, If I understand correctly, you are trying to create a link for each true value in this list using the Match function. However, it does not work that way. Match function will retrieve the first item that matches your criteria, thats why your...

Excel: index formulation

For K5 (and subsequent), sticking with the method you are using I d suggest =MATCH(TRUE,INDIRECT( J &K4+1 & :J60000 ),0)+K4 I don t follow what process you are trying to follow for getting a formula in column N - I cannot see what the logic is so if...

Excel: Separating Data in Excel

Aha! OK, here is what to do. Bear with me, there are some steps involved. Select the tab with the data. Make sure it is setup so that you have: - 1 row containing the names of each column (Name, country, email, ...) - for each person, 1 row of data...

Excel: split between 2 sheets

Muhammad: Assuming you are using Excel 2007 or later (and both spreadsheets you want to view are open - they must be separate files), click on View from the ribbon. Select Arrange All . Vertical will be side by side; Horizontal will be top and bottom...

Excel: Summing from several tabs formula

Gail, One problem could be that the entry in D14 on sheet May may be a text string stored as a number. You can test this by going to sheet May and in any blank cell put in =isnumber(D14) or from any sheet =Isnumber(May!D14) If it doesn t return...

Excel: VBA writing

Christopher Mitchell, Sub abc() Dim sh As Worksheet, sh1 As Worksheet Dim r As Range, cell As Range Set sh1 = ActiveSheet Set r = Range( B1 , Cells(Rows.Count, B ).End(xlUp)) For Each cell In r Set sh = Nothing On Error Resume Next ...

Excel: Write a link to the next sheet

Ian, If I read you code, on the first activesheet, you find the last cell with data in column A and then overwrite that cell and anything in that row with a link to the next sheet then you dropdown one row and write a formula to link to the cell above....

Excel: 9 Box Tool - Multiple Entries in each box

Absolutely. Each of the individual boxes has it s own formula in it. Just insert the extra rows you want to make it 30 cells tall. Then copy the last formula cell down the rest of the way within each of the grids. Be sure not get the formulas mixed up,...

Excel: add list

easiest is to copy the range to a new place, remove duplicates, add to combobox1: Sub Answer() Range( B1:B1000 ).copy range( Z1 ) change Z1 as you see fit Range( Z1:Z1000 ).RemoveDuplicates Columns:=1, Header:=xlNo change to xlYes if there are headers...

Excel: Changing Text to Number format for Huge data

Hi use this macro Sub TexNum() Range( ZZ1 ) = 1 Range( ZZ1 ).Select Selection.Copy Range( C2:AZ10000 ).Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _ SkipBlanks:=False, Transpose:=False Range(...

Excel: Changing Text to Number format swiftly

Hi Jay, I believe the following macro should do what you want. Note that it specifically processes only the range C1:AZ10000, but this is easy to adjust in the code. Sub TextToNumber() Convert the designated range text-formatted values to ...

Excel: Copy data of sheet1 to sheet2

Hi again Nabam, Sorry about misinterpreting your question. The picture you attached is exactly as I had assumed all along, and it worked for me with the data in that form, so it seems there are two possible reasons for it not working for you. 1. I assumed...

Excel: copy row if value macro

I would normally do this with an array filter (which is formula based) but you specifically asked for a macro solution. do you want the macro to run when one of the cells is changed, or is to run via a button? I ve ASSUMED that it should be when V1 is...

Excel: countif of dates

my GUESS is that you want to have a summary of every month - if so, I d put in a helper column which has the formula =A1-day(a1)+1 where A1 represents the cell with a date - this will return the 1st day of the month of A1 - copy this formula down,...

Excel: countif of dates

Back to the COUNTIFS() suggestion, as per your example I demonstrated ONE formula for one specific month. You would create a different formula for the next month, and so on. You could put the starting date of a given month in a cell, then use that as a...

Excel: countif of dates

Moshe The simplest way might be to use helper cells that convert the value in column A to a month number and then use the countif formula to count the number of 1 s, and 2 s and 3 s, etc. For example assume we make column C our helper cell column. The...

Excel: date formats

Interesting - I was going from what I could see in AllExperts, and the numbers were correct based on my interpretation. However, as long as you have it working that s all that matters. A little trick I used for this one is to build up the formula one bit...

Excel: if not is error formula

the number of brackets in your typed formula is wrong, but that may just be typing =IF(NOT(ISERROR(SEARCH( Water Set ,A2))), W/S ,IF(NOT(ISERROR(SEARCH( Decanter ,A2))), Oth Dkw ,IF(NOT(ISERROR(SEARCH( Dinner Pl ,A2))), Dinn ))) would be the corrected...

Excel: Excel VBA

Alexey, the property CopyObjectsWithCells was introduced in Excel 2013. So it would raise an error in Excel 2010. You have on error set so this may be hiding where your actual error is. You should remove or comment out error handling when you debug....

Excel: Excel VBA Change Conditional Format Line Weight: Follow-up

Hi Sam, Let me know if this is what you had in mind: Private Sub Worksheet_Change(ByVal Target As Range) Dim TI As Range Set TI = Intersect(Target, Range( D5 )) If TI Is Nothing Then Exit Sub Cell D5 has changed. Check...

Excel: Excel VBA Change Conditional Format Line Weight

Hi again Sam, Actually, the Change event will trigger upon a multi-cell paste. My previous code just hadn t considered this as a possibility. Here s an update to the code that includes handling for multi-cell pastes as well as the row 2 to 109 issue....

Excel: Format

Hi Karen, There s one possibility where you are pasting a cell pointing to a range beyond the sheet. Eg. In source sheet, you have cell C5 containing a formula =A1 . Now, when you try to paste in a target sheet in a different location, say C4, then excel...

Excel: formula syntax

Christopher, Assuming what you posted works, then =IF(J1 K1, ,IF($L$3= , ,IFERROR(INDEX(name,SMALL(IF(LEFT(group2,LEN( source data !$L$3))= source data !$L$3,ROW(group2)),ROW(A1)),1), ))) Since the original formula appears to be an array formula,...

Excel: help in functions

Hi Salem, I am unable to duplicate your problem. For me it only updates when the cell c changes. When I filter columns on the sheet, add/or delete filters the time does not update. I suggest you email your file to me so I can see what is happening and...

Excel: Indexing or Cross Referencing Cells Conditionally?

Hello Dean, I need to understand the question a bit more specifically. Especially the conditions which you are using for getting the result in column E. Can you mail me a sample copy of the file. Its a bit difficult to read the way its currently posted...

Excel: Macro to highlight a range if it contains ticket ranges

Bimmy, If you just want to strip out spaces you can use the trim command Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim s as String Dim RngToCheck As Range Set RngToCheck = Intersect(Sh.Columns(3), Target) If Not...

Excel: Mark cell based on textbox value

Mithila, When you say Forms textbox I assume you mean a non-activeX textbox - picked from the shapes menu. Sub Pickname() Dim res As Variant res = Application.Match(ActiveSheet.TextBoxes( Textbox 1 ).Text, ActiveSheet.Columns(1), 0) If Not IsError(res)...

Excel: Match 2 columns return 3rd with a difference

Angelique I may not be understanding your question, but----- You can create a unique serial number, by combining the data in the sales doc column and the item number column, by using the & formula, and then make sure in your matching formula that the result...

Excel: Need help with VBA code for Excel 2000

Hi again Vickie, Okay, here s a likely fix. I can t be sure because I no longer have Excel 2000, but please give this a try. Change Sheet2.Protect ... to Worksheets( Sheet2 ).Protect, both instances. This should keep Excel from being able to catch the...

Excel: Need help with VBA code

How about this: ============= Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rngCell As Range, dblTime As Double, icolor As Integer Sheet2.Unprotect Password:= Timesheets101 Application.EnableEvents = False On Error...

Excel: One Spread sheet from Another person

I m not sure I understand what you want to do - IF the file is on a shared network location - so you can both access it, then a formula would do it =[BusinessMileageSpreadsheet201516.xls]mileage!$B$24 where BusinessMilageSpreadsheet201516.xls is the...

Excel: Same text in a cell on one tab reproduced in a certain cell on another tab

To get the data from one cell to repeat in another cell you need to use a formula - my original answer showed you how to do this - or VBA - which I ve assumed would be overkill in this instance. If you require the data to be in multiple tabs, you will have...

Excel: VBA to List all Possible Combinations of Variables and Variants for each variable

Bob, If you want to know how many combinations you would have, then assume A 10 B 5 C 7 E 2 so you would multiply the number of variants 10 x 5 x 7 x 2 = 700 You can see with 10 columns this would get big fast. as far as writing them out...

Excel: Cant open word or excel in windows 10

I haven t had this problem (I m pleased to say) but I also haven t installed windows 10 yet - I tend to err on the side of caution and let others get the bugs out first! I ve checked and it has been reported, and there is a Knowledge base article - http://answers.microsoft.com/en-us/office/forum/office_365hp-word/were-sorry-but-word-has-run-into-an-error-that-is/917d6dfb-3a51-42f8-82fa-e949d5947d82/?auth=1...

Excel: change by val target

Hi again Dave, Yes, some functions that push data to worksheets do not trigger the worksheet change event. Let s hope that yours does trigger the worksheet Calculate event when A1 changes. Then the following code should work. This code should replace...

Excel: change by val target

Dave, Sounds like you want to use the change event. Right click on the sheet tab for the sheet where you want this behavior and choose View Code from the popup menu. This will take you to the visual basic editor and you will see the code module...

Excel: Conditional formatting

Hi John, You can use Conditional Formatting to get this done. To create the first rule: Select cells A1 through F##. Do this by dragging from A2 to A##. Then, click Home Conditional Formatting New Rule. In the New Formatting Rule dialog box,...

Excel: Conditional formatting using Vlookup

Hi Ajay, You can t use match to serve that purpose. (Unfortunately, Match is the most misunderstood function in excel). You can use a combination of vlookup, if, ISERROR and conditional formatting. If I can suggest a simple approach, you can create...

Excel: converting a formula to a number

I m puzzled - though by the way =a1+b1 is the same as sum(a1+b1) - BUT sorting the data SHOULD work - I do it regularly, and have no issues - the cells with formulas are still numbers - is it possible to see some sample data? My email is support@flameenterprises.co.u...

Excel: IF + COUNTIF?

yes, it is clear, and the sumproduct function does exactly that - the link mentioned using the multiplier sign, I tend to use double negatives, but the principle still works =sumproduct(--(data!a2:a2000=1),--(data!c2:c2000=1)) would give you a count...

Excel: IF + COUNTIF?

Moshe I would solve this by using a sumifs formula which will allow you to sum based on the meeting of multiple criteria. Your criteria are--- 1 does it meet a certain week, and 2 is it old or new. First we need to have a column in your comparison sheet...

Excel: create labels from badly organized spreadsheet

As you will be merging the data (I assume) into Word, you actually don t need to worry - as the data is either in one place OR the other, you can simply insert fields for both side by side - the blank data will merge as a blank (and therefore not be present)...

Excel: excel 2007 inventory increment

bobby, The methods I am familiar with working with an barcode reader and excel use the barcode readers ability to simulate keyboard entry. So using a different barcode reader would not be detected by excel. So the input method is going to input to the...

Excel: EXCEL 2010 Problems with "Open File"

I ve not experienced this (though I haven t got windows 10 - I m not rushing to upgrade, I ll wait until some more of the bugs have been ironed out) but this DOES sound like another bug - I ve had reports of issues with office 2013 and Windows 10 requiring...

Excel: excel 2013 formula

is this one formula per row? If so, =IF(AND(OR(O12=4,O12=5),J12=2), Error , OK ) in row 12 would give you the answer you want - replace my error and OK messages as you see fit. IF this wasn t what you meant, let me know - if it helps, my direct email...

Excel: excel addition and subtraction using barcode scanner

bobby, again, you don t need any vba code for what you describe.. You can use formulas. For entries in column A =countif(A:A, grey tshirt )-countif(A:A, grey tshirt 1 ) so you are reading in a separate barcode to indicate subtraction. if...

Excel: excel inventory

bobby, I showed you how to add in your onhand inventory. At this point, if you want to send me your workbook, which I assume has a working sheet where you put in your barcode data each data and a master sheet where you want to accumulate your inventory....

Excel: excel inventory

I m not 100% sure I understand the question, but it SOUNDS as though you want to store a value by updating a single cell without using a formula? IF so, the only way to automatically do this would be via VBA (i.e a macro) - it may be clearer if I saw a sample...

Excel: Excel mail label question

HOPEFULLY you now had a working answer, as I know you asked me for the answer as well - question found in question pool as other expert was either unable or unwilling to help. IF it hasn t been resolved do let me know by posting a follow up question and I...

Excel: Follow-up from previous question

You might have some luck inserting that formula into MOD(). =MOD(OriginalFormula, 7) ------------------ I understand that MOD is not a BASE replacement, but based on the context of your question that your original formula only returns 0-9 and you want...

Excel: Formula: Depending on Which Sheet a Cell is Selected from Using Another Formula

Evelyn, this worked for me: =INDEX(CHOOSE({1,2,3}, 2014-02-17 !A1, 2014-02-18 !A1, 2014-02-19 !A1),MATCH(G1,CHOOSE({1,2,3},INDEX(hours17,MATCH(MIN( 2014-02-17 !B4:B27), 2014-02-17 !B4:B27,0)),INDEX(hours18,MATCH(MIN( 2014-02-18 !B4:B27), 2014-02-18 !B4:B27,0)),INDEX(hours19,MATCH(MIN(...

Excel: Formula Query

I understand the concept for checking if employees have taken certain training courses or not. It appears on one reference sheet you have listed two columns of training courses (A and B). On the REPORT tab it appears your column C is important. In your...

Excel: Grouping data, creating pivot charts

1-Without actually having a file to work with, this is tough to really know what you reasking for. 2- Single customer ? Your example has ONLY single customers (assuming Client ID = customer) 3-no way this can be resolved by tomorrow since I just got this...

Excel: Input mask for time entry

This link http://www.pcmag.com/article2/0,2817,2316755,00.asp will give you a way of doing what you want - you MIGHT also like to know of the keyboard shortcuts you can use for current date and time. NOTE that the method outlined in the link will not actually...

Excel: Linking Excel

Hello Angela, Depending on your requirements, there are multiple options. 1. You can create formula links between the Separate reports and Full report so that whenever the Separate Reports are updated, the Full report will be automatically updated. This...

Excel: Look up

it s difficult to determine how the data is stored, but in essence I think you are going to want to do an index match to return the value RATHER than a lookup - index match returns a row within a table, so it s easy to return the values one row and two rows...

Excel: Macro to copy data from different workbooks to one workbook

Divi, in this macro, I assume you have four workbooks 1.xlsx 2.xlsx 3.xlsx 4.xlsx each with a single worksheet in them. I copy the worksheet to a single new workbook. You get a dialog to select whichever directory you want to process. ...

Excel: Macro to delete contents of green cells

Gail, It depends on what causes them to be highlighted in green and what color of green. If someone has manually highlighted the cell, and it matches vbGreen then you could do Sub clearcells() Dim cell As Range For Each cell In ActiveSheet.UsedRange...

Excel: Reformatting numbers downloaded as text

Almost any other character would have been easy, but the asterisk is a wild card - so doing a search and replace doesn t work as it simply replaces everything in the document. IF the data is all in one column, you can use data, text to columns to split...

Excel: select cells by format 'number'

Henk-Jan, No implicit ability to filter for format beyond the support for color that I am aware of. You can create a column next to your column of data and use the worksheet function Cell =Cell(A1, format ) then drag fill that down. (I used...

Excel: Separating data into workbooks and sheets

1) SHEETS to WORKBOOKS (1) Here s a macro for taking a multi-sheet workbook with formulas and creating individual formula-free wbs from each sheet. The date is added to the workbook names to give a reference as to when the wbs were created. http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/parse-functions/each-sheet-to-wb...

Excel: simultaneously search and sum up, update results in excel

Linify, Sorry for leaving off the sheet2 name. But glad you figured it out. Here is how I would look at Sheet2, Sheet3, Sheet4, and Sheet5 In B2 of Sheet1 =Sumif(Sheet2!$A:$A,$A2,Sheet2!B:B)+Sumif(Sheet3!$A:$A,$A2,Sheet3!B:B)+Sumif(Sheet4!$A:$A,$A2,Sheet4!B:B)+Sumif(Sheet5!$A:$A,$A2,Sheet5!B:B)...

Excel: SORTING DATA FROM ONE SHEET TO ANOTHER

I m never overly keen on solutions which duplicate data (in your case, the use of an autofilter would work the same as having seperate sheets) BUT it is perfectly possible. There are two methods - one uses formulas and is what I ve tended to refer to as an...

Excel: Sorting multiple columns sequentially in Excel 2010

Lesley, If I understand what you want, assume A is in cell A2. so in D2 put in the formula =Min(B2,C2) then drag fill down column D. Now sort your data using column D (select all your data and do a custom sort using the data in column...

Excel: TTR Formula to Determine if SLA Met

Kathie Sorry to be late in answering--I am answering for Richard who is not well at the present. I am not an excel expert, Richard suggests that you try reformatting the time cells. Hope this works for you, but if it doesn t please reask the question...

Excel: updating a cells value

I don t know python or c++ so that analogy didn t help me. I don t get where the 3 came from in automatically subtract the 3 from A1 . Is it always 3? If so, then in B1 all you need is =A1-3. Otherwise, I m not following what you d want to see in B1....

Excel: vba extract tables from multiple word doc to excel

Hi again Natasha, Thank you for the clarification. Based on this I believe the code below should do what you want: Sub ImportWordTables() Dim wdDoc As Word.Document Must set Microsoft Word 15.0 Object Library in References Dim wdFileName...

Excel: VBA writing

Chris Mitchell, go into the VBE (Alt+V11). In the project explorer find the Thisworkbook entry for the project you are working on. Right click on that and select view code. paste in code like this: Private Sub Workbook_Open() Dim r As Range,...

Excel: auto move rows

This question appears to have been posted across the web in multiple locations. This is nice for you, but will certainly waste the time of SOMEONE on one or more of those duplicate locations, people who are helping others for free would definitely be disappointed...

Excel: Carpet Plot

Hi Emmanuel, Strictly speaking there is no carpet plot capability in Excel. However, there is contour plot capability which I suspect is what you actually have in mind. An example of a contour plot would be a map with lines or colors indicating different...

Excel: one cell has 2 formullas

Nahid Batarfi, AND is a logical function. It would be used if your were testing conditions example =if(And(A1 =10,A1= 20, A1 between 10 and 20 inclusive , A1 either 10 or 20 ) if you want to get the total for two ranges you would use sum =sum(A1:X1,E1:E10)...

Excel: Conditional formattin formula

Lymari I will assume you have a cell that indicates the expiration date of the policy. For my example I will assume the cell is D7. Using the conditional formatting/new rules/use a formula to determine which cells to format/ put in the following formula...

Excel: Conditional Text

Esther, Best I can offer is to look at your worksheet if you want to share it with me. You can make a dummy worksheet that reproduces the problem (and remove any company confidential data) if you wish. You can send it to twogilvy@msn.com (tomorrow I...

Excel: Excel multiple index match funtion

Sadly, on AllExperts, the attachments are pictures too small to read - reading the question as posed NOW, it s not an index match, because you want to return ALL values that match - at which point, it s an array filter (as I call it). I do have examples...

Excel: Excel/VBA

compare: which replaces the value in B1 with Aaaa Bbbb . with: This in cell A1, doesn t in B1 and work in C1; select A1, hold down Ctrl, select C1, Copy; select E1, Paste. Cells E1 and F1 contain This and work , respectively doesn t seem...

Excel: Help with excel index

The pictures that come through on AllExperts are virtually impossible to make sense of - possibly my age! - I m sure the question should be possible to answer - would it be possible to see a sample file - my email is support@flameenterprises.co.uk - question...

Excel: hiding the formula bar

These are the commands for hiding and unhiding screen elements, even in Excel 2010. Sub HideStuff() Application.DisplayFullScreen = True Application.DisplayFormulaBar = False ActiveWindow.DisplayWorkbookTabs = False ActiveWindow.DisplayHeadings =...

Excel: lookup a single criteria and return multiple rows

This can t be done via formulas. You need VBA. Press Alt/F11, then Insert/Module, then paste in this code: Sub Fillit() i = Cells(Rows.Count, 1).End(xlUp).Row With Sheet1 n = .UsedRange.Rows.Count ag: .Range( $A$1:$E$9 ).AutoFilter...

Excel: lookup a single criteria and return multiple rows

There are no formula that automatically change your pre-existing entries on sheet2. To do this with formulas only I would think you could do 1 code at a time only. You would type a code in A2 and all the rows would appear for that code. To see another...

Excel: Matching Identical cells

Johnathan, I didn t suggest that your put the formula in A2. Recall: Put that in say C2 of your sheet1 and drag fill down the column so you would put in the formula to discover duplicates in another currently blank column. You will need the formula...

Excel: I need help creating the correct function in 2003 Excel

Hi Michael, Thank you for the question. For this item: (1) Somehow I need to combine them to make sure the ordered items are not more than what I have in stock but I do not want the inventory to be visible on the order form. On a separate...

Excel: Personal.xlsb or ?

Hi again Joe, Yes, you can put the macros in MACRO.xlsm and run them from your app that is a different workbook. Let s say you have a macro named MyMacro in the MACRO.xlsm file. To run MyMacro from your app: Workbooks.Open Filename:= C:joeappMACRO.xlsm...

Excel: Pivot Tables

it s not totally clear from AllExperts what the question is - or at least, what data you had and what you needed to do. To get days on the market, it s a simple X-y - where y is the starting date, and X is the date you want to stop calculating (so presumably,...

Excel: Refining the Subtract Function in VBA

Hi again Rich, This version should eliminate the problem with using entire columns (e.g., subtract(A:A,B:B)). Option Base 1 Function Subtract(A As Range, B As Range) As Variant Subtracts every cell in range B from entire range A and yields a...

Excel: repeat formula vba

DAVE, I would approach it this way (if I properly understand what you are trying to do) Make a copy of your workworkbook/worksheet and run the macro on the copy until you are sure it is doing what you want. Sub SIZE_PERMISSION() Dim wsP As Worksheet,...

Excel: Slicer

Tim, the slice itself has not visible or hidden type property. However, it is a shape and that does have such a property. You can try something like this code: Sub HideShowObjects() If ActiveSheet.Range( AF1 ).Value Then ActiveSheet.Shapes(...

Excel: sorting

Certainly is unexpected results. When you use F8:G8 and do the sort, if you watch carefully, you will see that it sorts as you want at first, then the formulas kick in and the calculation is restored . If you turn off automatic calculation,you will see...

Excel: VBA

Christopher, This assumes no merged cells and that any numerical data is entered as a number constant (not produced by a formula). Sub abc() Dim r1 As Range, r2 As Range Dim r As Range Set r = Columns(4) Set r1 = ActiveSheet.UsedRange Set r = r.Resize(,...

Excel: VBA

Chris, You only need to change one command. See below. Sub abc() Dim r1 As Range, r2 As Range Dim r As Range Set r = Columns(4) Set r1 = ActiveSheet.UsedRange Set r = r.Resize(, r1.Columns.Count - 3) Set r2 = r.SpecialCells(xlConstants, xlNumbers)...

Excel: More Than 256 Columns?, spreadsheet component, professional package

spreadsheet component, professional package, excel sheet: Vinnie, I m sorry to say that you can t have more than 256 columns in a spreadsheet. Users have been asking this of Microsoft for many years, but this is a hard-and-fast limit. Excel retains several things from its old, ancient past, and one of them is...

Excel: 2D surface plot, temperature contours, plot 2d

temperature contours, plot 2d, plot area: khalid Excel manages the height of the plot area based on what other options you have in the chart. Put the plot on a worksheet as an object and make the whole chart higher. That is about the only thing I could suggest. -- regards, Tom Ogilvy ...

Excel: 3 axis graphs, line graph, series names

line graph, series names, time span: Well, Imtiaz, I m not sure what you tried and why you didn t have success, but I can tell you an easy way to add a third data series to a chart. It s very simple: 1) Select the cells that make up the third data series. 2) Press Ctrl/C to copy them....

Excel: 3 macros made into one, hi bob, macros

hi bob, macros, many thanks: What s wrong with just putting it in one sub?: sub DoEverything() show all named ranges Dim nm As Name For Each nm In ActiveWorkbook.Names nm.Visible = True Next unprotect all worksheets Dim Password Password = 1 Dim WS As Worksheet...

Excel: 3 Qs, q1, q3

q1, q3, q2: 1 - you can t. Pictures are only background for the entire worksheet (Format/Sheet/background), or they sit on TOP of the cells. 2 - Don t know what you mean. 3 - If you mean print remote areas on one sheet, you can use the camera tool to take a picture...

Excel: 3 question about Excel form., using ms office, currency type

using ms office, currency type, category c: From the first question, it looks like you have some macros going on that trigger that behavior, as that is not standard in Excel. Check the macros, and you may be able to deactivate that checking. If there is no macros, then I may have misunderstood your...

Excel: 32000 data points in Excel, chart source, source data

chart source, source data, cheers: Hi Greg, How are you? I have tried to draw a chart with source-data located in A1:B32000 and there is no problem. Large charts do not depend only on Excel version. Tgey depend on your RAm, on how many program you are running at the same time etc. ...

Excel: I have 4 different excel sheets..., column c, c10

column c, c10, a2: You use MATCH, Annie. Let s say that your master list is in A2:A100. The names to compare are in B10:B120. You want to find the names in B that don t occur in A. Follow these steps: 1) Click in C10 and enter this formula: =IF(ISERROR(MATCH(B10,$A$2:$A$100,0)),...

Excel: I have 4 different excel sheets..., vlookup function, column c

vlookup function, column c, a100: Annie You can use the vlookup function to find the names that are matched on the various lists. first the names you are looking into to look for the match need to be sorted in ascending order. then the formulas can look something like this. In the...

Excel: 4 Quadrant graphs, business school student, series options

business school student, series options, grid lines: Monil, You sound like a business school student! Here s how to do the old 4-box matrix. In Excel I have the following values in the following cells. a1 - blank a2 - Price High a3 - Price Low b1 - Low Quality b2 - $10 b3 - $30 c1 - High Quality...

Excel: 40Stand.Dot error message, xlstart folder, word templates

xlstart folder, word templates, office folder: It sounds like a Word template file (they have .dot extensions) may have somehow gotten into your Excel startup directory. Do a search on your hard drive for any folder(s) called XLSTART. Anything in the XLSTART folder is automatically opened whenever you...

Excel: 5 questions on excel, arrow key, blank row

arrow key, blank row, right arrow: 1 - Copy/Paste each range (sorry). 2 - ctrl/click the sheets you want to print, then print. 3 - Sub Copy3() Sheets( Formula ).Rows(3).Copy Range( A65536 ).end(xlup).offset(1).Paste End Sub 4 - much harder: Dim Was As Integer Private...

Excel: 500+ Documents, Same Type of Change, lastname firstname, count documents

lastname firstname, count documents, question word: No problem - this macro should do what you want Sub test() Dim holdvar As String, checkvar As Long Dim fs As FileSearch Set fs = Application.FileSearch With fs .LookIn = C:My Documents change to actual path .FileName = *.doc If...

Excel: More than 7 if statements, array formula, e4

array formula, e4, boundary conditions: Hi Sekaledi, Hope you are doing well. Interesting question - took me some time to figure it out but this should work. This is an array formula - To enter it, you need to press Ctrl+Shift+Enter rather than Enter. The formula is =IF(E4 =500,E4+1, IF(E4...

Excel: What is $I$8 in excel formulas?, excel formulas, dollar signs

excel formulas, dollar signs, absolute reference: $I$8 refers to the cell I8. The dollar signs make it an absolute reference. That means that when the formula is copied, it will always continue to refer to I8. Example: the formula =I8 refers to cell I8, but if you then copy that formula to the...

Excel: More than 8 if's, vlookup, v75

vlookup, v75, u75: You are correct; VLOOKUP will not work in this situation since you are using Horizontal values. In addition, as you probably have discovered, you cannot have more than 8 nested IF functions. The solution? HLOOKUP instead of VLOOKUP. I suggest adding...

Excel: 95/98 Excell, sum function, spid

sum function, spid, support microsoft: Cannot be specific with an answer on this one, as there are a number of possibilities - not least, that the size of the spreadsheet could have got too big for the calculations - but this link http://support.microsoft.com/search/default.aspx?query=manual+calculation&catalog=LCID%3D2057&spid=&qryWt=&mode=r&cus=False&x=11&y=9...

Excel: 97 Excel formula, lotus 123, formula evaluation

lotus 123, formula evaluation, excel formula: What is Proof- per worksheet- F Summary ? I am surprised that it worked at all in a workbook because EXCEL does not allow concatenation to build a formula, but 1-2-3 did. Something HAS changed from one workbook to another - you just have not seen...

Excel: 98 version to 2003 version, anne troy, reference libraries

anne troy, reference libraries, excel 98: That s a loaded question, Ken. There are many differences. The best thing to do is to develop in the lesser version, which would be (I think) Excel 97 (you keep mentioning 98, but that s Excel for MAC, not PC). When you have problems, copy the line that...

Excel: On Format, conditional formatting, format button

conditional formatting, format button, asistance: Yes. You ll want to use conditional formatting for that. 1. Select the range where you want the B s to appear red. 2. On the Menu toolbar select: Format|Conditional Formatting 3. For condition 1 set: Cell Value Is , equal to , and the letter ...

Excel: Aargh, files excel, backup feature

files excel, backup feature, rare occasions: Don t open the recovered files. Close that tab, and re-open the original ones -- they should still be as you left them! Also, to be safe, you can use the Always save backup feature: In eht Save As dialog, there s a Tools dropdown at the top, and a General...

Excel: Access to my CDROM folder., draft manuscript, writeable drive

draft manuscript, writeable drive, cd burning software: The question isn t an Excel question, it s really either an operating system or a Cd question - HOWEVER, a CD can be burnt to but cannot always be regarded as a writeable drive by windows- IF you have software set up that makes it a virtual drive, then you...

Excel: Access the Date Picture Taken attribute, cell b2, scripting runtime

cell b2, scripting runtime, filepath: If, for instance, you have on your drive a picture in .jpg format, you can use vba to retreive the date that .jpg file was created. This can also be done with any other type of file. Here s code that will prompt you to browse to a .jpg file, and will then...

Excel: How To Access Excel 97 Macro ?, excel 97 macro, macro names

excel 97 macro, macro names, macro code: I suspect the code is an old style Excel 4 macro. This would appear on macro sheets which are probably hidden. These macros work in conjunction with defined names and these are probably hidden as well. This is just a guess of couse. I have provided code...

Excel: Access a WebSite Internet from VBA, maeby, continuous line

maeby, continuous line, default browser: Bero, your name doesn t sound familiar, so no problem. The code for opening a web page - as a web page in your default browser - looks like this: ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _ http://www.allexperts.com , TextToDisplay:=...

Excel: Accessing closed Excel Files, excel 1997, progress summary

excel 1997, progress summary, backward compatibility: 1 - there are new features in Excel 2003 which don t exist in xL97 - trying them in XL97 would create an error, if it d compile at all. 2 - I d find a temporary unused cell to contain the formula to pick up the value, something like this: Function theval(ThePath)...

Excel: Accessing ranges in closed workbooks in custom functions, builtin functions, excel forum

builtin functions, excel forum, custom functions: I posted a message telling you you received a reply from Harlan Grove. In general, VBA does not support accessing information from closed workbooks. So if you built a user defined function (UDF) to be used in the worksheet, it will not work with a closed...

Excel: Accessing ranges in VBA, line sheets, excel worksheet

line sheets, excel worksheet, vba: I opened a new Workbook, named a sheet A , and in the VB editor I created a module, and a sub with the line: Sheets( A ).Range( Q2:R32 ).Font.ColorIndex = 2 and then ran it, and it ran fine, no problems. So sorry, I can t re-create the problem you...

Excel: Accounting Aging, current date, spreadsheet

current date, spreadsheet, invoices: Kara: I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/aging.xls It is how I would creating an aging report where specific invoices are separated into columns based on their age. It uses IF functions (as you expected)....

Excel: Accumulate Total/Running Total, column c, ogilvy

column c, ogilvy, excel 2000: Cheryl, The short answer is there is no builtin capability to keep a running total in a cell. What exactly do you want to do A B C D E item1 10 3 1 8 someone wants 2 of item 1. Do you want to type a 2 in column C ...

Excel: Activate macros, security method, macro security

security method, macro security, design mode: You re out of luck. Even if a user or you choose the low security method then you or the user can always change the level at any time. To my knowledge, you can not turn off the design mode or at least I have not found a way to do it. You can exit the...

Excel: Activate Spell Check Button, spell checker, mispelt

spell checker, mispelt, check button: Hi Curt! How are you? I am afraid that you cannot activate the spell checker in a protected sheet. The spell checker allows you to change the contents of the cells (at least those mispelt). Therefore if the cells are protected against editing and writing...

Excel: Activating a Macro by clicking a cell, two different things, typical actions

two different things, typical actions, crtl: You are saying two different things. You want to run ONE macro or choose from a list of more than one macro to run with a click or ctrl/alt click? Those are two different things. And the more cells you want to click in that would run the macro means the...

Excel: Active X Buttons Resizing, button changes, font changes

button changes, font changes, work sheets: Presumably the Controls are from the Controls Toolbar. As you say the size can be set in the Properties of the Control, however, if you right click on the Control and Choose Format Control you will see a Format Control Dialog, check the properties Tab and...

Excel: ActiveX Combo, listindex, value case

listindex, value case, hi bob: works fine for me -- feel free to send a copy of the file to me at bobumals@yahoo.com. Also... Your entire Combobox1_Change event could be rewritten as: Private Sub ComboBox1_Change() ChDir J:Metro CentresMetro Centres - GeneralDO NOT TOUCHBristol...

Excel: ActiveX Warnings in Excel 2002 (XP), using activex controls, those keys

using activex controls, those keys, microsoft support: Amine, this isn t something I have had much experience with, so the best I can do is refer you to this article at the Microsoft Support site: http://support.microsoft.com/kb/827742/en-us You are prompted to grant permission for ActiveX Controls when...

Excel: Actual/360 amortization formula for excel, amortization formula, excel formula

amortization formula, excel formula, original loan amount: Nicole I don t know of a different way of doing it. I don t get any diffeence between what calculates using your excel formula and what I calculate using a separate program that I use to calc interest rates or payments or present value. If I use your...

Excel: ADDRESS of a range, st petersburg florida, trinidad west indies

st petersburg florida, trinidad west indies, cell values: Emmar01 I m not sure exactly what you are trying to do with the range, but it sounds as though you would like the following if A1 has go in it and A2 has stop in it you want a formula that will produce gostop as a result of putting the cell values of A1...

Excel: ADDRESS of a range, douglas m smith, trinidad west indies

douglas m smith, trinidad west indies, cell values: Hi, No, ADDRESS can only be used to reference a single cell. However, you can concatenate a couple of ADDRESS results together (with the help of ROW, ROWS, COLUMN and COLUMNS) to get what you want: =ADDRESS(ROW(Beam_Range),COLUMN(Beam_Range))& : &ADDRESS(ROWS(Beam_Range),COLUMNS(Beam_Range))...

Excel: ADO, jet oledb, findfirst method

jet oledb, findfirst method, adlockoptimistic: I would use the FINDFIRST method to find the record - if NOMATCH then you add a record otherwise you update it rs.findfirst Primary key = & range( A1 ).value replace A1 with the actual cell, or offset cell, you are searching if rs.nomatch then...

Excel: ADODB Speed decrease from MS Excel 2000 - XP upgrade, ms excel 2000, array pointer

ms excel 2000, array pointer, oracle driver: Dimitri Sorry for the late response. Was out of town on business. I really don t work with ADODB to any extent, but my suggestion was based on my personal experience in general. I did find that command to find the recordset size suggested by someone else...

Excel: Adapt VBA to selection instead of entire worksheet, irow, colum

irow, colum, macros: Since macros are and can only be written in VBA then how someone refers to his/her macro/VBA knowlegde will vary. They are one and the same. Since I can t see and have not seen the data then I am driving blind here. Be sure to save the file before running...

Excel: Adavnce Filters, np, list price

np, list price: NP I m not exactly sure what the data looks like, but the advance filter is available using the same criteria for either column, with different results because of the different values in each column. I m not sure what else to tell you, not knowing what the...

Excel: Add horizontal line to custom menu, custom menus, custom menu

custom menus, custom menu, horizontal line: Hi there, the BeginGroup = True is the only way I know how to add the horizontal line. If you are looking for code on how to program that: .Controls.Add(Type:=msoControlButton).Caption = my stuff... .Controls( my stuff... ).BeginGroup = True...

Excel: Add hours to a date, whole numbers and decimals, many different things

whole numbers and decimals, many different things, datedif: Hi Bridget, That s a great question. In Excel time is represented in whole numbers and decimals. The number 1 represents one whole 24 hour period. So when you are trying to add the number 20 (for 20 hours) you are telling excel to add 20 (24 hour) periods...

Excel: Add-ins, vlookup, addin

vlookup, addin, ogilvy: You can certaionly put information in an array. If it is a lot of information it might be arduous setting it up. Dim myArray(1 to 10, 1 to 2) myArray(1,1) = A MyArray(1,2) = Apple myArray(2,1) = B myArray(2,2) = Boy . . . myArray(10,1) =...

Excel: how to Add sum.if, itis, ogilvy

itis, ogilvy: =sumif(A:A,9,B:B) entered in C1 would add the values in column B where the corresponding cell in column A contains a 9 as an example. This is how itis written in English. perhaps in your regional copy of excel it is written =sum.if(A:A;9;B:B) --...

Excel: Add a warning to a macro, ogilvy, excel 2000

ogilvy, excel 2000, message box: Hello Diane, Sub MyMacro() if msgbox( Are you sure ,vbYesNo) = vbNo then Exit sub existing code End Sub You can make it snazzier with If msgbox( Are you sure ,vbYesNo+vbCritical) = vbNo then exit sub look at the help on msgbox for other...

Excel: Adding Additional Info to Column, excel spreadsheet, stacy

excel spreadsheet, stacy, cells: stacy If it is the same text all the way down the column you can use this formula in a new column =a1& your text and copy that down, then copy and replace the original column. If it is different text or not all cells it needs to be done manually. ...

Excel: Adding an app alert, data validation, message tab

data validation, message tab, input message: Hi, Robb, I guess you didn t notice either in my expert listing or on the question page where I say that I m not familiar with VBA. But there s a way to do this without using any macros at all. Let me know if this works for you: 1) Select C4. 2)...

Excel: Adding blank rows to Excel, blank rows, excel tools

blank rows, excel tools, blank window: Lori, the best way to do this is with VBA code. Don t worry, I ll walk you through the steps. First, open the file. Now press Alt F11 to open the Visual Basic Editor. Now, on the left side of the screen, you should see a project named based on the current...

Excel: Adding blank rows, blank rows, blank row

blank rows, blank row, k1: Yes - my own invention, I might add! In an unused column, say K, enter 1 in K1, 2 in K2, use fill handle to take it to the bottom. Say that s K150. Now select & copy K1:K150, then click in K151 and paste. Now you have the values 1 to 150 twice, from K1 to...

Excel: Adding built-in menu item to a custom menu, public const, custom menu

public const, custom menu, pagebreak: Diana, I noticed reading some answers to other questions at Allexperts that you appear to not have an answer to your question. Also, it appears your question has taken on some new details. You can not put the PageBreak button on the main menu at the top...

Excel: Adding button function, dropdown arrow, data validation

dropdown arrow, data validation, button function: There a lots of ways to do this. One is to present a Userform which has option buttons, or a listbox, or a combo box, etc. Another is to present an inputbox in which you would type the value you want. Another is by using Data/Validation and entering a list...

Excel: Adding button to spreadsheet, vba programming, programming code

vba programming, programming code, hot button: Jim, The button probably drives a macro a.k.a. VBA programming code. Assuming the sheet or workbook is not protected, you can verify this by right clicking on the button, clicking Assign macro, then Edit. This will start the VBA editor. You will probably...

Excel: Adding A Cell Of Different Sheets To Column, ur time, macros

ur time, macros, cells: I would suggest using a for..next loop as you KNOW how many times you want to go through the loop, and there is no real need to select the UserPage to set the formula - this code should do what you want (If I ve understood the question correctly) Private...

Excel: Adding Cells in Multiple Worksheets, excel 5, excel 2000

excel 5, excel 2000, soma: Hi Bryan How are you? There is no sortcut for that. You can use =Sum( Sheet1!a1,Sheet2!a1,.....Sheet20!a1) Which is very boring !! Or write a VBA Function 1. Tools -macro -Visual Basic Editor 2. Insert - module 3. copy Function Soma(a As Range)...

Excel: Adding cells with boders, cell borders, rcell

cell borders, rcell, count cells: Hello Jaya, I assume you are referring to ColorFunction as found on http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm. I further assume that you want a function with the same functionality as ColorFunction except that it checks to see if all the cell...

Excel: Adding cells from different worksheets, j11, expert data

j11, expert data, single quote: when you have spaces in the filename, use the single quote =SUM( My Sheet 1:My Sheet 3 !J11) or for your examples =SUM( Sample Data Sheet:Expert Data Sheet !J11) If it gets squirrely, make sure you don t have any leading or trailing spaces in the...

Excel: Adding cells in different worksheets, douglas m smith, sheet names

douglas m smith, sheet names, brainbench: Hi again, Yes, multiple words need to be between single tick marks: Los Angeles !A1 Do a point and click reference to one of these sheets and you will see it. I try to avoid spaces (or dashes or other odd characters) in sheet names for this reason....

Excel: Adding characters to raw data, quotation mark, al1

quotation mark, al1, atk: Here s a VBA solution -- select all the cells to be converted first. Sub Untested() For each thing in selection thing.value= & thing.value & , Next End Sub And you re right -- leading quotes disappear. To see it, you need 2 (the...

Excel: Adding column, datasheet, spreadsheet

datasheet, spreadsheet: ...Since Result s sheet manipulates the data,... What Result s sheet are you referring to? You only mention two sheets, PresentationSheet and DataSheet. REMEMBER, I can not see what you see and ALL that I know ia what you tell me. You will need to...

Excel: Adding column, blank rows, conditionals

blank rows, conditionals, missing something: I used specialcells(XlCellTypeLastCell) - and UNFORTUNATELY that s exactly what it does! It s the same as when you press Ctrl End to get to the end of the document. An alternative is to use endcellvar = WorksheetFunction.CountA(Range( A:A ))+1 the...

Excel: Adding a continuing total, cell c, cells

cell c, cells: Jim There isn t a simple way to do this as you are updating cell A everyweek and would need a macro or vba script to take the amount from cell a and add it to cell b. If you are taking the amounts from another sheet then you can do it using simple formula...

Excel: Adding custom menu, public const, wn

public const, wn, window application: Yes you can use the new toolbar approach but I think your best bet there too would be to build it and then delete it as my approach here does with manipulating the menus. If I remember correctly, building and/or using a new toolbar causes EXCEL to either...

Excel: Adding Delete Function, budget spreadsheet, alpha bravo

budget spreadsheet, alpha bravo, b5: Jim, First, write down all the cells that you want cleared with their worksheet names. For example, I have 3 worksheets named Alpha, Bravo and Charlie. On Alpha, I want cells A4 and B5 cleared; on Bravo I want cells A6 and C4 cleared; and on Charlie I...

Excel: Adding dashes to SSN, format cells, social security number

format cells, social security number, menu format: You are really close, substitute the row where the actual transformation is done with something like: Orig.Cells(I, 1) = Left(Orig.Cells(I, 1), 3) + - + Mid(Orig.Cells(I, 1), 4, 2) + - + Mid(Orig.Cells(I, 1), 6) As another option, if you only need...

Excel: Adding dashes in txt cell, format cells, blank cell

format cells, blank cell, 123456789: Cristal, As long as they are stored as numbers, select the cell, then go to the menu and do Format= Cells, go to the number tab. There may be an option toward the bottom for SS#. If so, use that. If not, select custom and enter 000-00-0000 then...

Excel: Adding to data to the next line, value application, rng

value application, rng, excel 2000: Assuming you are using Column A, use End(xlUp) to find the Range & the total for the sum. This code will find the range used & the next empty cell. Then use the WorkSheet Function Sum to get the Total and place it in the last cell. Sub AdRng() Dim...

Excel: Adding up data using sumif, array formulas, array formula

array formulas, array formula, cell a2: You are on the right track, but I believe the easiest way to do this will require an array formula. If you are unfamiliar with array formulas, they are very similar to regular formulas except instead of using just one reference [such as right(A1,1)] you can...

Excel: Adding date, time to minutes, chip pearson, cell reference

chip pearson, cell reference, adding time: Kevin, it depends on what they enter in the cell. If the enter A1: 8/27/07 7:00 am B1: 0:30 then C1 with a formula like = A1+B1 and formatted like A1 would be 8/27/07 7:30 am Dates and time are one data type. If my sheet looks like this:...

Excel: Adding an element to an array, redim, ubound

redim, ubound, bob hope: This works -- does it help? Sub arrays() Dim n() ReDim n(3) n = Array(#1/1/2006#, #2/2/2006#, #3/3/2006#) ReDim Preserve n(3) e = #12/12/2007# n(3) = e For i = LBound(n, 1) To UBound(n, 1) Debug.Print n(i) ...

Excel: Adding same ending to each word.., ogilvy, a2

ogilvy, a2, f2: Assume the first item in in A2 in another column, for example E2, put in a formula like =A2& -s.jpg in F2 =A2-l.jpg Now select E2:F2 and drag fill down the column for 2800 rows. then select column E:F and do Edit= Copy, then Edit= Paste Special...

Excel: Adding equations into excel via Visual Basic, excel in vb, excel spreadsheet

excel in vb, excel spreadsheet, c4: The recorder is always a good place to start to give you the syntax you need (even experienced use it and is not beneath them to do so) and this case it would have helped you. What you need is Cells(34, 11).Formula = =A1+B1 or Cells(34, 11).FormulaR1C1...

Excel: Adding hours over 24 in Excel, douglas m smith, mm ss

douglas m smith, mm ss, time values: Hi Ian, Time values are really just day fractions. Example: 6:00:00 (6 hours) is really the value 0.25 (6 hours / 24 hours). If you multiple the result of your B2-A2 calculation by 24, you will see the total hours in a decimal format. If you want to...

Excel: Adding up hours in excel, hh mm ss, excel user

hh mm ss, excel user, mm format: Lisa, It is very easily fixed. Because time is a fraction of one day, when the work period spans two days, you don t add 24 (hours), but just add 1 (day). So this formula now work =IF(C3 B3,C3-B3-D3,C3+1-B3-D3) But You don t need those shenanigans...

Excel: Adding Info from different tabs, left hand side column, left hand side

left hand side column, left hand side, cell a1: Hi Diana, The formula gets a bit long in order to add the count from all the sheets, but still manageable. I assumed that the column you wanted to look at in Tab1..Tab5 were all the same (column A), but I m sure you can look at the formula and see how to...

Excel: Adding ISERROR to this formula:, email filters, spam folder

email filters, spam folder, sorry for the delay: Hi Chris, Sorry for the delay. I changed my email filters and my questions got dumped in the spam folder instead of the inbox. I m assuming you want to test C12 with ISERROR. If so, the formula becomes: =IF(OR(C12=0,ISERROR(C12)), NA ,IF(ABS(((B12-C12)/C12)*100)...

Excel: Adding a % increase to a spreadsheet that contains absolute numbers only, blank cell, absolute numbers

blank cell, absolute numbers, raw data: Yes, there is actually a very convenient way to hand this by following these steps: 1) Select a blank cell and type 1.05 into that cell. 2) Copy that cell 3) Select the area, raw data, that contains the values you want to increase by 5%. 4) Click edit-...

Excel: Adding Label In Excel Using VBA Code, functions in excel, ssssss

functions in excel, ssssss, exit function: Sorry for the late response, I did answer three days ago, but somehow it never got through the system. Basically, the functions in Excel are limited on how they can affect the environment, so you won t be able to create a label using a function. That is...

Excel: Adding Leading zeros to a dataset in order to sort, ascii character code, 5rc

ascii character code, 5rc, sort criteria: If this column starts in A1, enter this in B1 then fill down, then sort both by column B. This assumes the only suffixes are L, C, or R: =TEXT(LEFT(A1,FIND( - ,A1)-1), 00000 )&TEXT(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1, L , ), R , ), C , ),FIND( - ,A1)+1,255),...

Excel: Adding ListBoxes Dynamically depending on numeric entry, ms office 2000, version ms

ms office 2000, version ms, vba code: ¿ . Hi, May Back from my weekend, I have just seen you question. I developed a little VBA code which will accomplish what you are asking for. If you agree, please, follow these steps: 1. Display the VBA Editor by pressing Alt+F11. 2. Insert...

Excel: Adding multiple sheets and naming them in a macro, tring, exact name

tring, exact name: Is this to be done in a NEW workbook? You did not say. If not, then waht happens the current daily sheets? Is the first sheet to be for the first day in the month? Again, you did not say. Give me an example of a sheet name you need. You were not clear...

Excel: Adding the numbers within "one" cell?, cell c1, column c

cell c1, column c, b1: Hi Terry, Place the following formula in cell C1 and drag the fill handle down as far as you want. This formula will work up to a 7 digit whole number such as 2457139.315 it will ignore the numbers beyond the decimal point. =IF(LEN(INT(B1))=2,MID(B1,1,1)+MID(B1,2,1),IF(LEN(INT(B1))=3,MID(B1,1,1)+MID(B1,2,1)+MID(B1,3,1),IF(LEN(INT(B1))=4,MID(B1,1,1)+MID(B1,2,1)+MID(B1,3,1)+MID(B1,4,1),IF(LEN(INT(B1))=5,MID(B1,1,1)+MID(B1,2,1)+MID(B1,3,1)+MID(B1,4,1)+MID(B1,5,1),IF(LEN(INT(B1))=6,MID(B1,1,1)+MID(B1,2,1)+MID(B1,3,1)+MID(B1,4,1)+MID(B1,5,1)+MID(B1,6,1),IF(LEN(INT(B1))=7,MID(B1,1,1)+MID(B1,2,1)+MID(B1,3,1)+MID(B1,4,1)+MID(B1,5,1)+MID(B1,6,1)+MID(B1,7,1),...

Excel: Adding numbers subject to 2 conditions, array formula, sumproduct

array formula, sumproduct, numbers subject: You can use an array formula or sumproduct to do this array formula =sum(if(a1:a100= printer ,if(b1:b100= Laser ,c1:c100,0))) enter with ctrl shift enter - obviously replace a1:a100 etc with the actual ranges to check sumproduct =SUMPRODUCT(--(A1:A100=...

Excel: Adding an option to a custom menu, custom menus, page break

custom menus, page break, custom menu: Well, that s up to you to decide if it that will be easier since you are doing the coding but my suggestion should work - to mimic the Insert Page Break. If you record that code it should only be about 4 or 5 lines excluding how you get the user to tell...

Excel: Adding question, cell b2, cell c1

cell b2, cell c1, formul: Tony First I would use a formula in each column, on the same row going across, that if the date of the column is greater than the the current day, then it adds to that cell the goal for that day. This formula might look something like this. =IF(C1 TODAY(),+C3,...

Excel: Adding Row Values depending on Corresponding Rows, cat car, relative references

cat car, relative references, pivot table: You can use Data/Filter/Advanced filter to get the unique items (dog, cat, car) - select only the data containing all the dog/cat/car/dog/car...and in the filter use Copy to another location and also check unique values. Now you have the unique list somewhere,...

Excel: Adding Rows, added complication, ogilvy

added complication, ogilvy, spreadsheet: Hello Kylie, I guess you are asking about a macro Assuming your data starts in A1 and the numbers are in column A and the numbers are in sequential order and if alpha coded, the number is at the beginning. Works on the sheet active at the time you run...

Excel: Adding rows, string s2, cell1

string s2, cell1, steve test: Steve, Test this on a copy of your workbook. As I understand it you want to group on the first set of characters before the first period and your data is sorted. Sub PutInRows() Dim lastrow As Long, s1 As String, s2 As String Dim i As Long, l1 As...

Excel: Adding Sheets, microsoft office, different things

microsoft office, different things, microsoft: Richard You are almost there. Click in the cell you want the total in type in = move to the cell you want to add and click in it click on + move to the other cell Press enter You can also do the same thing with the sum button. Richar...

Excel: Adding Source Data to a Chart, graph chart, line graph

graph chart, line graph, plotting points: If you would like, email me the spreadsheet to rmadsen@iname.com. However, I may not be able to respond for several days because of a family emergency. Robert =========================================================== Although I enjoy answering your questions,...

Excel: Adding text to conditional formatting, visual basic programming, conditional formatting

visual basic programming, conditional formatting, excel spreadsheet: Hi Paul, It is possible to sort by color, Unfortunately sorting Conditionally formatted cells gets complicated and needs Visual Basic Programming. This goes beyond my capabilities. You can read about sorting by colors. This will note at the bottom of the...

Excel: Adding years to a column of dates, hiv program, pediatric hiv

hiv program, pediatric hiv, janet sullivan: Hi, Janet, This isn t a problem at all. It s a perfect situation for the handy DATE function. This function has the syntax =DATE(year,month,day). Let s say that you have a date in A1. The following formula returns a new date that is 14 years later: ...

Excel: Adding, sumation, true formula

sumation, true formula, f2: Karl, it sounds as if you are entering formulas into cells that have been formatted as Text. Unfortunately, just changing the format of the cells won t fix the formulas. You have to change the format, then enter the edit mode of the cells (press F2 or click...

Excel: Addins, custom macros, addin

custom macros, addin, analysis tool: I am unable to find the root cause, why your addin is uninstalled by itself. There following are the chances functions not to work: 1. conflict with other addins. try to unload all custom addins and try whether the functions work. 2. Make sure you don...

Excel: Addition from existing worksheets, cell b1, cell a1

cell b1, cell a1, valid numbers: Make sure that your sheets are named Sheet1, Sheet2, and Sheet3, and that they re in order (i.e., they re consecutive sheets with no other sheets, hidden or visible, between them). Make sure that there are valid numbers in cell A1 of each of these 3 sheets....

Excel: Addition to formula that removes time from date, format cells, decimal fraction

format cells, decimal fraction, time element: If you just need to SEE the date, then format the cells as the date format you want - this will resolve the issue, albeit leaving the time element in place otherwise, Excel stores dates and times as WHOLE NUMBER=Date, DECIMAL FRACTION=Time so =INT(AnyFormulaWithDate)...

Excel: Address function for cell range, douglas m smith, location f3

douglas m smith, location f3, cell location: Hi Armando, You are on the right track and your formula is very close to being correct. The problem the cell reference in F3 is text. The #VALUE! error indicates it wants a value of some kind, unlike other error messages which tell what the problem is....

Excel: Address retrieval, vlookup formula, search range

vlookup formula, search range, ogilvy: Hello Peter since the only specifics you gave were D4/D5 and a returned value of J22, I will surmise this representative situation: Assume your vlookup formula in D4 is =Vlookup(D3,$F$1:$K$100,5,False) where D3 contains the value to be looked up...

Excel: Address retrieval, vlookup, cell c2

vlookup, cell c2, cell c1: There are two ways I can think of to do what you request. I will present them both and you can determine which one you prefer. Please download and review the spreadsheet at the following location: http://home.swbell.net/nate-sus/excel/returnaddress.xls...

Excel: Address retrieval, vlookup function, cell reference

vlookup function, cell reference, match: For the cell reference I would use the function MATCH, with the same criteria as the VLOOKUP. You may need to add some number to reflect the number of rows before the range. For example, if your lookup range is I2:J50, and your criteria is in E2, the VLOOKUP...

Excel: Addressing '99999' values, blank cells, empty cells

blank cells, empty cells, line graphs: Hi Steve, If I am understanding your problem correctly, I believe that this will help: You would need to replace all 99999 values with blank cells. To do this you would: Select the range of cells you want to search. If you want to search the entire...

Excel: Addressing '99999' values, blank cells, line graphs

blank cells, line graphs, chart option: Hi Steve, There s no chart option built-in to Excel that will automatically conditionally handle values except for how to plot blank cells. (More on that in a minute.) You will need to process the information somehow prior to plotting it. There are a...

Excel: Addressing '99999' values, line graphs, sort field

line graphs, sort field, statistical calculations: Steve I don t believe there is a way to tell the graph that you want to ignore the 99999 data, but you might try manipulating the data before graphing it. How about sorting the data using the 99999 field as the sort field so that this data is isolated,...

Excel: Addressing '99999' values, line graphs, countif

line graphs, countif, sumif: Various thoughts spring to mind - one is to use the autofilter to filter OUT the rogue cases and copy the data to another (stats) page. Alternatively, replace 99999 with the text unavailable - this would give you a graphable with blanks dataset, and the...

Excel: Addressing '99999' values, line graphs, line graph

line graphs, line graph, statistical calculations: Steve, The only way I can think of would be to just have the SERIES formula in the line graph count the # of exact cells without counting the 99999 values. I doubt there is any other way (besides VBA). You can always put a letter or symbol before the...

Excel: Adjacent cell entry changes cell formula, douglas m smith, e5 f5

douglas m smith, e5 f5, cash checks: Hi John, Off the top of my head, the only thing I can think of that cause something like to happen would a macro that got triggered by entering something in column Y. I you can email me the spreadsheet (doug@abundant-solutions.com), I ll take a closer...

Excel: Adjusting range in formulas to accomodate new data, column number, occurances

column number, occurances, tedious task: You may want to send a copy of your spreadsheet to Lotus@swbell.net. If I look at the actual data I might be able to point out the best solution. That being said, there are a couple of things that come to mind... 1) Can you do a search and replace? For...

Excel: Advance charting, excel pivot table, performance detail

excel pivot table, performance detail, pivot tables: Hi Ghalib, You can use Pivot Tables to do this. I think it would be a good solution for what you are trying to do. You can select just the date range you want to see how much you invested. Have you ever worked with them before? If not, here is a brief...

Excel: Advanced Adding, sumproduct, arrays

sumproduct, arrays, a1: Steven The sumproduct function is not really a count function but one that multiplies components of arrays. Depending on the complexity of your need you should be able to us an IF type formula to do the things you mention I.E. add divide multiply, etc....

Excel: Advanced Addition, sum formulas, column c

sum formulas, column c, dollar amounts: Typically SUMIF and COUNTIF formulas are used when only 1 or maybe 2 criteria are being tested. For multi-criteria count/sum formulas you can use SUMPRODUCT. Assuming the dates are column A, names column B, and values column C and: A1:A10 = Dates B1:B10...

Excel: Advanced Counting Formula In Excel, a3, a2

a3, a2, a4: Steven Not sure what you are tring to do but you can use a formula something like this to calculate what should be counted within a range. +COUNT(C1:C10)-COUNTIF(C1:C10, 38293 )-COUNTIF(C1:C10, 38296 ) As you can see first we counted all the cells...

Excel: Advanced drop downs, incident report form, vlookup

incident report form, vlookup, combobox: ...working on a form in Excel ... Do you mean a worksheet that had been designed to simulate a form or do you mean a userform - something you use in macros only? Also, ...When I click on a certain crime, I want the statute number to fill in the next...

Excel: Advanced Excel 03 Question, sep oct nov, oct nov dec

sep oct nov, oct nov dec, jul aug sep: Question: So I can reference 12 different worksheets with this and it will show only the information that typed into the latest month? Answer: The design of the formula I used essentially refereences all 12 worksheets (it actually just references 1 worksheet...whatever...

Excel: Advanced Excel Formulaes, excel analysis toolpak, stample

excel analysis toolpak, stample, column c: Roger: I believe I understand what you are looking for and I suggest the following formula: =IF(B1= Private , Private ,IF(ISNUMBER(C1),+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1& : &C1)),2) 6)),C1)) I also wanted to make you aware of a formula called...

Excel: Advanced Filter, filter tool, filter criteria

filter tool, filter criteria, filter auto: Harv: I have uploaded a spreadsheet here which follows the discussion: http://home.swbell.net/nate-sus/excel/advancedfilter.xls 1) Make sure you are on the “advanced filter” tab of the spreadsheet. 2) I started off by creating a small table of data...

Excel: Advanced Filtering (I think), cell a2, ab2

cell a2, ab2, blank sheet: Matt, this can be done with formulas. Let s do this in two steps. I will start with just getting the names in the 27 columns + the one additional column for matches in both D and K. Then you can tell me where you want to go from there. (assuming my solution...

Excel: Advanced ffilter, douglas m smith, stock example

douglas m smith, stock example, brainbench: Hi Ahmed, You will need to to run the Advanced Filter from the 2nd sheet, referencing the list range on the first sheet in the dialog box, something like this: Sheet1!$A$1:$C$6 Douglas M. SMith Brainbench MVP for MS Excel www.brainbench.com www.abundant-solutions.com...

Excel: Advanced formatting in Excel 2000, graveyard shift, excel 2000

graveyard shift, excel 2000, vba: write a bit of VBA in the worksheet s macro module to pick up the time of update. Private Sub Worksheet_SelectionChange(ByVal Target As Range) if hour(now) 20 then do something for the graveyard shift target.interior.colorindex = 3 endif do something...

Excel: Advanced formula needed, microsoft office 2003 professional edition, microsoft office 2003

microsoft office 2003 professional edition, microsoft office 2003, d9s: The below formula will sum all of the tabs in a spreadsheet where the value in D9 is not equal to 3.3: =SUMPRODUCT(SUMIF(INDIRECT( Sheet &ROW(INDIRECT( 1:4 ))& !$D$9 ), 3.3 )) NOTE: The above formula is clean, but it only works if your tabs are named...

Excel: Advanced formula, row sum, stock purchases

row sum, stock purchases, transaction broker: My applogies for the slow reply...I thought I had answered this but I guess I forgot to click send ... I must admit I have never used Quattro Pro, so I m going to have to just do some guessing on this response:] Here is the Excel formula in question:...

Excel: Advanced number formatting, format cells, conditional formatting

format cells, conditional formatting, custom category: It s a format, not a formula. You need to use Format/Cells/Number tab, Custom category, and enter the format there ( the [red][ 80] Too Low ;... ). Conditional formatting OVERRIDES other formatting; it doesn t merge, so you can t get 6-7 formats this way....

Excel: Advanced IF Statements, c 70, bugbear

c 70, bugbear, xls: Jeremy The If statements will only go to 7 nested ifs generally and changing the ones that go beyond that is a bugbear. Have a look at this link which has a small xl file set up for your type of marking system and it is easily editable. http://www.pcexperthelp.com/howto/vlookupgrades.xl...

Excel: Age Calculation in years, douglas m smith, cell a1

douglas m smith, cell a1, cell b1: Hi Sherry, Dates are really numbers in disguise. As far as Excel is concerned, today (June 14, 2004) is 38152. Knowing this, you can do math with dates like any other numbers. If your specific date in in cell A1 and the person s birthdate is in cell...

Excel: Age Formula, datevalue, thanks in advance

datevalue, thanks in advance, ym: =DATEDIF(A1,DATEVALUE( 7/1/05 ), y ) & years, & DATEDIF(A1,DATEVALUE( 7/1/05 ), ym ) & months, & DATEDIF(A1,DATEVALUE( 7/1/05 ), md ) & days or, if you put 7/1/05 in another cell, say A2, you can use =DATEDIF(A1,A2, y ) & years, & DATEDIF(A1,A2,...

Excel: Alarm, cell b1, cell a1

cell b1, cell a1, conditional formatting: Anthony One way I can think of of doing what you want is as follows: You have two conditions that if they exist you want to be notified by excel. condition 1=date march 1 or later condition 2=cell blank The following IFAND formula reflects those...

Excel: Alert, conditional formatting, ms outlook

conditional formatting, ms outlook, aidan: If you want it to be Outlook, you will need to use VBA to set up an appointment item (this is possible!). If you want Excel to alert you with a colour change, conditional formatting would handle this. I ve not really got enough information to be more specific...

Excel: Align Checkboxes, arrow button, horizontal spacing

arrow button, horizontal spacing, left arrow: Hi Luis, SHIFT+CTRL+Left Click to select the multiple checkboxes. Go to the Excel menu and select View then Drawing toolbar. From the Drawing toolbar select Draw then Align or Distribute then Distribute Vertically to evenly space the checkboxes...

Excel: Allow data changes but prevent formatting changes, spreadsheet model, paste values

spreadsheet model, paste values, vba programming: You re right, Michael, Excel does not have this inherent ability. If a cell is locked, neither can be done. If a cell is unlocked, both can be done. Here are a few workaround ideas I have: 1) Teach the users how to do a Paste Special which pastes values...

Excel: Alphabetizing using Macro, sheet tab, rng

sheet tab, rng, companyname: Bryan, Right click on the sheet tab and select view code. Paste in this code: Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim rng As Range If Target.Count 1 Then Exit Sub If Target.Column = 4 Then column D Set rng = Cells(Rows.Count,...

Excel: Altering pivot, cell d2, column headings

cell d2, column headings, free column: Here s an example. First, create a range that defines your 4 date buckets. In range A1:A4, enter: 1/1/08 4/1/08 7/1/08 10/1/08 Now let s say you have a column of dates starting with cells C2, C3, etc. In cell D2 (or any other free column, enter...

Excel: Alternate rows, hey john, best bet

hey john, best bet, excel 2002: Hey John, I can definitely write a macro to do this for you. I just want to make sure you are comfortable using a macro for your solution. If you have no experience with them, don t worry, I can give you detailed instructions. :o) Let me know if you...

Excel: Alternatives for Viewing Long Data Sets in Rows, format cells, vertical format

format cells, vertical format, text feature: Hi Cyndi, Have you used the Wrap Text feature before? Format/Cells/Alignment (Check the box that says wrap text.) You should then be able to resize the column to a more desirable width. You may also need to resize the height of your rows, but you...

Excel: Amortization Formulas for 30/360 vs 30/365, amortization formulas, spreadsheet formula

amortization formulas, spreadsheet formula, using microsoft office: I don t have the opportunity to work much with EXCEL s financial functions and therefore I am not that familair with them as I am with others. But to get a more complete set of functions then click Tools, Addins and select the Analyis ToolPak. This will...

Excel: Amortization table with interest only, amortization table, balloon payment

amortization table, balloon payment, google: Yes, it would I m sure be possible to do BUT I m afraid the maths etc is outside my area of expertise - possibly posting the question in the lounge area of www.wopr.com could help, or use one of the excel newsgroups (see groups.google.com to find these) -...

Excel: ANDing columns of text data, array formula, curly brackets

array formula, curly brackets, 5th column: Assume your data starts in A1:C11. D1: =IF(ROW() =(COUNTA(A:A)*COUNTA(B:B)*COUNTA(C:C)),INDEX(A:A,TRUNC((ROW()-1)/(COUNTA(B:B)*COUNTA(C:C)))+1,1)& &INDEX(B:B,MOD(TRUNC((ROW()-1)/COUNTA(C:C)),COUNTA(B:B))+1,1)& &INDEX(C:C,MOD(ROW()-1,COUNTA(C:C))+1,1),...

Excel: ANDing columns of text data, array formula, curly brackets

array formula, curly brackets, 5th column: First the joined data. In D1, enter =A1&B1&C1 Then just copy down. For non-matching names In E1, enter =IF(ISERROR(SMALL(IF($B$1:$B$20 ,IF(COUNTIF($A$1:$A$20,$B$1:$B$20)=0,ROW($A$1:$A$20), ), ),ROW($A1))), , INDEX($B$1:$B$20,SMALL(IF($B$1:$B$20...

Excel: Analysing multiple lists, composite analysis, pivot table

composite analysis, pivot table, loop through: The analysis could be done using a pivot table, but of course this cannot happen on a group of worksheets. I don t think you can easily use formulas to achieve what you want, but a macro would be pretty easy to create - basically, it would need to loop through...

Excel: Anchoring columns on different sheets, empty cells, paste link

empty cells, paste link, column right: Hey Andrea, Here is a fix for your problem: 1 - Select the Column that has the values, 0 and U. 2 - Click on Format on the top menu, click Conditional Formatting. 3 - In the box on the left, click Cell Value Is. 4 - In the second box from the left,...

Excel: Animation in Excel Worksheet, excel workbook, excel worksheet

excel workbook, excel worksheet, sentences: Here is some example code - you would call StartBlink whenever you want to start the process and StopBlink when you want to stop it (so for example, have start blink called from the Open event of the workbook Public RunWhen As Double Sub StartBlink()...

Excel: AOL & Excel attachments, doubleclick, word document

doubleclick, word document, asking this question: Hi Jill, I am not familiar with AOL either, but in my Groupwise email environment at work, there are some who can open excel or word straight from email and others who have to slide it to their desktop then doubleclick to open. I m sure there is a direct...

Excel: Apend data from multiple spreadsheets into one master spreadsheet, master spreadsheet, master cells

master spreadsheet, master cells, getopenfilename: With the master sheet active, press alt/F11, then use Insert/Module, then paste this in: Sub Gather() Dim Master As Worksheet Set Master = ActiveSheet MsgBox Point to the folder containing all the files in the next dialog - then click Cancel...

Excel: Append with leading zeros, zeros, excel 97

zeros, excel 97, punctuation: since it s text, you need to use a formula, not formatting. in another range, say E2, enter =if(len(A2) 19,REPT( 0 ,19-LEN(A2))&A2,A2) and fill down. you d have to define punctuation for the 2nd question. If it s to remove periods, simply edit/replace...

Excel: Multiple sheets selection, test xls, selection move

test xls, selection move, sheet1: Move the worksheets one at a time independent of selecting them. The code blow assumes that s is a worksheet in the active workbook and test.xls is loaded in memory. Robert Dim s As Worksheet Dim o As Worksheet Set o = Workbooks( test.xls ).Worksheets(1)...

Excel: Appending Info, spreadsheet template, expert listings

spreadsheet template, expert listings, excel expert: Gosh, Teresa, since I have no idea how your macro is working, and how you re currently summarizing the data, it s very difficult for me to advise you how to add on to it. If you want to continue using the macro, I suggest you ask someone who has expertise...

Excel: Appending from multiple worksheets, sh1, blank cell

sh1, blank cell, ubound: Sub copydata() Dim v As Variant, sh1 As Worksheet Dim sh As Worksheet, i As Long Dim r As Range, r1 As Range v = Array( Sheet1 , Sheet2 , Sheet3 , Sheet4 ) Set sh1 = Worksheets( Summary ) For i = LBound(v) To UBound(v) Set r = sh1.Cells(Rows.Count,...

Excel: Appending Records, using ms office, parmesh

using ms office, parmesh, paste operation: Parmesh Question 1--I m sure it can be done, but I do not know the method to use- I suggest you reask of another allexperts person and I m sure someone will know how. One thought would be to split the data when you import so that you know it will fit...

Excel: Application.Dialogs(xlDialogOpen).Show, csv file, comma delimited

csv file, comma delimited, valid dates: To the best of my knowledge, if the file is opened using VBA, then the dates are interpreted as US English format if they can be, particularly if the file is a .CSV file. Perhaps you will need to write you own code to open and parse the file to interpret...

Excel: Application-defined or object-defined error, microsoft office 97, msgbox

microsoft office 97, msgbox, validation: Hi Gabriel How are you? Please try one of the following: Sub hhh() Set sh = Sheets(1).Range( a1 ) On Error GoTo xxx MsgBox TypeName(sh.Validation.Formula1) = string xxx: MsgBox no validation was predefined End Sub or Sub hhh1() On Error Resume...

Excel: Application events, range a11, manager updates

range a11, manager updates, arrow keys: I m not sure I would have approached this the same way, but that s not relevent! I ve not used the SheetChange event myself, which may be why I ve not run into this problem, but that event does return the target - you can see this in use by using Private...

Excel: Application.match, target value, tip archive

target value, tip archive, match: Before you clear the target, save its value in a variable. e.g. Dim targValue targValue = Target.Value Target.ClearContents Now, if you want the code to check for that same target value in a different range (we ll assume a range named TipArchive2),...

Excel: Applying a custom template to an existing document, capabilities of microsoft excel, ns dept

capabilities of microsoft excel, ns dept, excel spreadsheets: KEVIN Sorry I don t know of a way that you can accomplish what you want, but I would be surprised if it cannot be done. I d reask the question of another allexperts person and check the resumes for someone who might have more experise in that area than...

Excel: Applying $ to formulas, formulas in excel, cell location

formulas in excel, cell location, cell reference: Not sure you ve use vba before, but that d make it easier. Press Alt/F11, use Insert/Module, paste this code in: Sub MakeAbsolute() For Each x In Selection x.Formula = Application.ConvertFormula(x.Formula, xlA1, xlA1, True) Next End Sub...

Excel: Appostrophe S&R, accent mark, vlookup

accent mark, vlookup, several different kinds: Find and Replace is how you will want to get rid of the apostrophes. The only reason I can think as to why it doesn t work is because there are a several different kinds of appostrophes. There is the one that prints on your keyboard, , there is an accent...

Excel: ARROWS, toolbars tab, excel spreadsheet

toolbars tab, excel spreadsheet, shap: Adding the arrows is simple. There is a drawing toolbar, so if you go into Tools Customize, and select the Toolbars tab, you can check that toolbar and it will appear in the bottom left of your Excel window. In this tool bar you will find an item called...

Excel: Arbitrary date changes in Excel?, douglas m smith, macintosh version

douglas m smith, macintosh version, date system: Hi Boris, Your IT helper is mis-informed. The Windows version of Excel starts counting dates at January 1, 1900, but the Macintosh version starts at January 1, 1904. Here is your four year difference. To allow an easy way to fix this difference (not...

Excel: Arbitrary date changes in Excel?, excel 2002, sp 2

excel 2002, sp 2, boris: You do understand that I have no way of determining what might be causing this problem because you did not tell me how the dates are put in the workbook to begin with. Are formulas being used, are the dates being put there via macros, etc? Since I can not...

Excel: Arbitrary date changes in Excel?, date function, excel 2002

date function, excel 2002, sp 2: Without seeing an actual sample of the spreadsheet, I can only speculate. I am not aware of a bug in Excel that causes spontaneous date changes. Could it be that your dates are being generated somewhere using the date() function or something similar? If...

Excel: Area under a graph, mathematical graph, area under the curve

mathematical graph, area under the curve, math problems: From your description it sounds like what you are looking for isn t a standard label Excel includes. You would need to calculate with a formula and have Excel lay that data on top of your existing graph (or as a trend-line, etc, your preference). http://home.swbell.net/nate-sus/excel/chartarea.xls...

Excel: Area under a graph, excel 2002, graph

excel 2002, graph, cells: I would do that calculation outside of the graph, as the two points are in some cells before they go to the graph, and you can use them on a formula to calculate the area. Maybe this is not what you look for, can you tell me more details on what you want...

Excel: Area under a graph, douglas m smith, rough approximation

douglas m smith, rough approximation, approximate area: Hi Emmar01, No, you would need to perform that calculation with the data used to generate the chart. Using calculus, it s the integral of the chart formula or basically adding the area of an infinite number of slices of the area below the curve. Here...

Excel: Area & Volume estimating, cubic feet to cubic yards, triangle base

cubic feet to cubic yards, triangle base, circle diameter: Not clear what you re asking -- you want a generic function that knows the shape of the object in question and you pass it the measurements? Your example uses feet & inches for the input yet the output is in cubic yards -- how should this function know the...

Excel: Area & Volume estimating, cubic feet to cubic yards, recognizable shapes

cubic feet to cubic yards, recognizable shapes, triangle base: Hey Bob, Are you asking for what the formulas would be for area and volume for the shapes you listed? If so, I believe this will help: Area: Square/Rectangle = length * width Circle = pi * radius squared Triangle = base * height / 2 Hexagon = 2.598...

Excel: Arranging data in ranges, blood sugar level, patient ids

blood sugar level, patient ids, distinct rows: perhaps with a combination of the FREQUENCY function and Pivot tables it can be done, but without seeing the layout of the real daya, it s hard to guide you. Feel free to email me a sample workbook at bobumlas@yahoo.com and re-describe what you want to do....

Excel: Array in Excel, a9, array

a9, array, value formula: If you highlight just this part of the formula: IF(A2:A9= yes ,B2:B9,0) then press F9, you ll see {10;14;0;0;8;10;0;23} and all the zero s are included in the average, To get what you want, change the formula to =AVERAGE(IF(A2:A9= yes ,B2:B9)) (no ,0...

Excel: Array Formula, array formula, sumif

array formula, sumif, f 350: I am not sure I understand your question but EXCEL has an add-in named Conditional Sum Wizard. Install it and use to create a SUMIF using more than one criteria. Then change the SUMIF to COUNTIF. EXCEL sometimes does not behave properly when using COUNTIF...

Excel: Array Formula?, array formula, selection boxes

array formula, selection boxes, pivot table: Bruno: Well, array formula might be the way to go if you wanted a count of the number of policies that meet the above criteria (or a sum of the total units to be transferred), but it appears you want a list of SKUs where a transfer will be made and the...

Excel: Array Formula, douglas m smith, array formula

douglas m smith, array formula, cell reference: Hi Robert, Here s the standard array formula set up for your problem: {=SUM(IF(LEFT($A$1:$A$3,2)= X: ,$B$1:$B$3,0))} Be sure to use Control-Shift-Enter to make it an array formula and have the {} brackets show up. You can replace X: with a cell...

Excel: Array Formulas, array formula, array formulas

array formula, array formulas, blank cells: For the benefit for other volunteers on this site who volunteer their time for free to help, then consider including a copy of the formula you are having trouble with and a small sample of data as well. It is impossible to determine what is causing a...

Excel: Array Formulas In Excel97, array formulas, array formula

array formulas, array formula, array entry: If you hit a limit, you can fool excel into surpassing the limit, by using extra parentheses: Excel thinks this formula has 5 parameters: =SUM(A1,B2,C3,D4,E5) but thinks this formula has 2 parameters: =SUM((A1,B2),(C3,D4,E5)) so there s practically...

Excel: Array formula and indirect, decimal separator, array formula

decimal separator, array formula, array formulas: The INDIRECT function expects a string as the parameter, so even if you are using array formulas here, it is transforming it to a single element, to fit that requirement. One way of doing this still using the array can be the following: =SUM(INDIRECT( G...

Excel: Array formula problem, array formula, data columns

array formula, data columns, sales territories: Bill The following may be the problem--this is straight out of the help Using an argument in an array formula that is not the same number of rows or columns as the range that contains the array formula will produce #N/A. If the array formula has been...

Excel: Array formula query, array formula, criterian

array formula, criterian, todays date: Hello Robert, So you want to count that row if the closed date is greater than the startdate + i or if the closed date is empty? =SUM((Team= Glasgow )*((ClosedDate Startdate+ & i & )+(ClosedDate= ))*(RecvdDate =StartDate+ & i & )) --...

Excel: Array as a formula result, array formula, ogilvy

array formula, ogilvy, cells: Jeff Can you see an array in a single cell? No. Can it return an array? Yes =Index(if(A1=1,Array1,Array2),3,5) as an example. (Array1 and Array 2 are names refering to 5 row x 10 column cell ranges the other thing you could do is use =if(A1=1,Array1,Array2)...

Excel: Array as a formula result, curly braces, array

curly braces, array, d2: Sure. First select all the cells to contain the answer, then enter the formula with ctrl/shift/enter. For example, Select A2:D2, then ctrl/chift/enter this: =IF(A1 3,{1,2,3,4},{5,6,7,8}) (note the curly braces around the numbers) Then put 1 in A1 & see...

Excel: Array formula to sum time, array formula, military time

array formula, military time, time format: Hi, John, Why do you have the sum in time format? Would you like the answer cell to show something like 54:75? The truth is that any number in time format cannot go outside of the clock (12:59 in regular time, or 23:59 in military time). I think that...

Excel: Array formula, array formula, array formulas

array formula, array formulas, b10: Alexa, Assume the three ranges are A1:A10, B1:B10 and C1:C10 (they don t have to be adjacent) then =SUM(IF((A1:A10 )+(B1:B10 )+(C1:C10 ),1,0)) Entered with Ctrl+Shift+Enter rather than just enter (since it is an array formula) worked for...

Excel: Array formulas, array formula, array formulas

array formula, array formulas, column headings: Gary, =SUM(((LEFT($B$1:$Z$1,3)= 502 )+(LEFT($B$1:$Z$1,3)= 504 )+(LEFT($B$1:$Z$1,3)= 506 ))*(A2:A1000= Wages )*B2:Z1000) entered with Ctrl+shift+Enter to make it an array formula. this is if you data is as you describe and it extends down from row 2 to...

Excel: Array Lookup to Find Closest Date and Next Closest Date, array formula, curly brackets

array formula, curly brackets, b monkey: for column C I would use c1: =Min(if($A$1:$A$200=$a1,$B$1:$B$200)) Entered with Ctrl+Shift+Enter (since this is an array formula), format as Date and then drag fill down column C in column D I would use D1: =MIN(IF(($A$1:$A$200=$A1)*($B$1:$B$200 C1),$B$1:$B$200))...

Excel: Array Question on Excel, example spreadsheet, array formula

example spreadsheet, array formula, false statements: Alissa: If you have an extra header row on the main worksheet, CounselingLog, then you won t need to modify the ROW() formula (If you have an extra header row on an individual spreadsheet, such as attendance, then the ROW needs a modifier). That being...

Excel: Array substraction, array formula, b cells

array formula, b cells, excel formula: Hi Albert, The only way that it is convenient to do this with worksheet formulas is if the array B is a row rather than a column. For example, let the array A be B2:B11, and B be F1:T1. Then an array formula can be placed in F2:F11 that computes the first...

Excel: Array Value Comparison, static array, array value

static array, array value, exit function: Function CityIsIncluded(City As Range) as Boolean CityIsIncluded=False for i=lbound(ArrMasterCity,1) to ubound(ArrMasterCity,1) if city.value=ArrMasterCity(i) then CityIsIncluded = True Exit Function End If ...

Excel: Can Arrays do Column A - B = Column C?, dealer discount, column c

dealer discount, column c, dollar amounts: Hi Jeremy,How are you? Please try the following 1. Type the selling price in A1:A10 2. Type the dealer discount in B1:B10 (in %) 3. Tools - Macro - Visual Basic Editor 4. Insert module 5. copy Sub Dealerprice() Dim P As Range Set P = Range( A1:C10...

Excel: Arrays with dates, array formulas, array formula

array formulas, array formula, sumproduct: You can use SUMPRODUCT, that is not an array formula by itself, but uses arrays as parameters. In this case, supposing providers are in range A2:A100 and the date in range B2:B100, you can use something like: =SUMPRODUCT((A2:A100= A )*(MONTH(B2:B100)=11))...

Excel: Arrays inside ListBoxes, boundcolumn, listboxes

boundcolumn, listboxes, column count: May, I don t know how useful this answer will be to you, but I would use the following code to display the 2 columns from the selection made in the first listbox. Private Sub OKButton_Click() Dim RowRange As Range RowCnt = 0 For r = 0 To...

Excel: Asking For VBA Help in Copying Range Values, run time error, range values

run time error, range values, project1: By including the path of the workbook in your statement, It appears that the two workbooks are closed. You can use straigt VBA to copy information from one closed workbook and you certainly can t use vba to update information in another closed workbook. ...

Excel: Assign Macro To a Button, c drive, spreadsheet

c drive, spreadsheet: Hi Robert, Can you lpease tell me what is the final purpose of this MACRO? Is it to open some file? Try this out-- and let me know if you need something else. Warm regards sub Open_file() Do filnm = Application.GetOpenFilename Loop Until filnm...

Excel: Assign macro run keys, shortcut key, macro name

shortcut key, macro name, excel vba: Alan, Hello. No, typing the shortcut combination in the comments of VBA will have no effect. To add or change a shortcut combination, click Tools Macro Macros and highlight the specific macro. Now click Options and verify or change the shortcut combination....

Excel: Assign value, countif, microsoft excel

countif, microsoft excel, score: Assuming your Category 1, 2, and 3 columns are Columns A, B, and C, try this: =COUNTIF(A2:C2, high )*3+COUNTIF(A2:C2, medium )*2+COUNTIF(A2:C2, low )*1 See my article for more on COUNTIF: http://www.officearticles.com/excel/count_using_multiple_criteria_in_microsoft_excel.htm...

Excel: Assigning Numerical Value to Letter, ntw, column c

ntw, column c, data values: Hi Steven, I am assuming that your data values are in a column, not in ONE cell. Two steps works well: 1. In column C (or any blank column), in row 2 (if that s where your data values start in column B) enter the formula: =IF(C2= NMC ,0,IF(C2=...

Excel: Assigning Values, cell a1, vlookup formula

cell a1, vlookup formula, current sheet: The way that I usually do this is by creating a table with the values assignment, with the value to be entered in one column and the value to be returned on the column to the right of it, and then use the VLOOKUP formula to return the desired value. For example,...

Excel: Attaching a word doc to a Excel mailing List, outlook object model, model in the world

outlook object model, model in the world, excel97: At the risk of sounding silly, why is the mailing list in Excel - why not a group contact in Outlook - then the only thing to do in word would be to send to mail recipient as attachment - this can be automated, and I THINK that it should be possible to automate...

Excel: Attendance sheet Question, array formula, attendance sheet

array formula, attendance sheet, coresponding: =sum(countif(range for 1 year,{ A , T , N , L }))-(sum(countif(range of last 4 months,{ A , T , N , L }))=0) It is unclear where this formula would be located or how many cells constitute a month, so this is about the best I can tell you. replace where...

Excel: AUTOMATING SEARCH IN THE SPREADSHEET, pivot table, customer one

pivot table, customer one, long long time: Esther, I will say that it is possible, even without seeing the workbook. However, it will probably require VBA code to execute, but you may be able to use a pivot table. I would need to know what the data looks like, and how variable it is. It would be...

Excel: AUTOMATING SEARCH IN THE SPREADSHEET, exact structure, customer one

exact structure, customer one, customer names: Esther, I assume that the Main Date is in one column, and the dates to be corrected are in another column. Is that right? Please write back and let me know the exact structure of the database. That is, tell me which column the customer names are in, which...

Excel: Audit Trail Problem, workstation pc, enough memory

workstation pc, enough memory, audit trail: I need to know more about the nature of the audit trail. Is it a worksheet? How is it generated? Are you perhaps looping in VBA code which you aren t aware about? Feel free to email me the wb with instructions as to how to replicate the issue if you wish --...

Excel: Audit Trail problem, workstation pc, memory message

workstation pc, memory message, mr gates: Graham In my experience the not enough memory message has been the result of the PC RAM, I ve never found the program itself (excel) to be the problem. It looks like in your case the 2 gig of RAM should easily handle the problem unless there are a number...

Excel: Audit trail problem, workstation pc, enough memory

workstation pc, enough memory, audit trail: I imagine the issue is with the volume of changes that are being recorded, particularly over the number of worksheets - might be worth saving AS another file name and trying from here, and could also be worth copying the sheet you want to analyse to another...

Excel: Auto Adjust Zoom VBA code, screen settings, acording

screen settings, acording, bit of fun: Hi Rodrigo DO you mean you want a macro which can detect the screen settings and then apply an automatic zoom ? The following code adjusts screen zoom. Its a demo and a bit of fun for you. Sub Zoomdemo() Application.WindowState = xlMaximized...

Excel: Auto adjust zoom with VBA Code, question pool, selection range

question pool, selection range, vba code: Rodrigo, I am not the person you asked this of at all experts - I found it in the question pool and it is pretty old (11 Sep), but in case you don t have an answer here is one approach Sub SetZoom() Dim r as Range Application.WindowState = xlMaximized...

Excel: Auto Calculating Dates, cell a1, date function

cell a1, date function: Hi Erik, I assume that the three dates you want to be generated based on 1/1/07 are 7/1/07, 1/1/09, and 1/1/11. Formulas you can use to generate these values given that 1/1/07 is in cell A1 are: =DATE(YEAR(A1),MONTH(A1)+6,DAY(A1)) =DATE(YEAR(A1),MONTH(A1)+24,DAY(A1))...

Excel: Auto Column Width, target cells, auto size

target cells, auto size, advanced excel: In the Visual Basic Editor, on the Worksheet object, use the following code: Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range For Each cell In Target.Cells cell.EntireColumn.AutoFit Next cell End Sub Each...

Excel: Auto-Complete /w Data Validation, cell a2, data validation

cell a2, data validation, drop down boxes: FYI - Corrected my original answer - I had the letters on my sample references wrong... Data Validation drop-down boxes don t incorperate an auto-complete feature; however, you can trick Excel into doing what you need. Your data-valadation list requires...

Excel: Auto cell copy, customer id numbers, up arrow

customer id numbers, up arrow, begining: you don t need another column, you can easily fill in the values that are missing in the current column: Select the Cust ID column, use edit/goto, click Special, click Blanks, click OK. When you re back in the worksheet, type =, then press the up-arrow and...

Excel: Auto changes in cells in Excel, excel 2002, apparent reason

excel 2002, apparent reason, spreadsheet: Peter, I am trying to replicate this problem. It seems to me that I only get this problem if I copy the cell with the formula in it, then paste it as a value (thus removing the formula), then access the cell as if to edit it again. When you do this, Excel...

Excel: Auto consecutive numbering, cell d1, format cells

cell d1, format cells, multiple times: The location of the code is critical. Right-click the Excel LOGO near the File menu, select View Code, then paste what I gave you into the window you re presented with. I also said that you need to change D1 to contain ONLY the number, not the W, and to format...

Excel: Auto-count/sort question, chris kelley, manual entries

chris kelley, manual entries, sheet tab: To reduce the number of manual entries into each of your COUNTIF formulas, reference the cells where you have your names instead of typing each person s name. For example: =COUNTIF(A7:A10, = &A1) where A1 is the first name you have typed. Change...

Excel: Auto Date Entry Removal, date entry, spreadsheet

date entry, spreadsheet: I m not sure if they have changed something in the 07 version, I am not working with that yet. If you havent tried changing the format to Text , try that, it works on my version. Also another way of entering the range is entering it as text for example...

Excel: Auto Date Formula, kind assistance, cell c

kind assistance, cell c, due date: I don t exactly what you mean by auto due formula, but here there are a pair of ways of dealing with dates: Use sums directly. The dates are stored as numbers, and every unit you add or subtract represents a day. For example, to add 30 days to the cell...

Excel: Auto Drop-down Menu for Navigation, visual basic programming, programming macros

visual basic programming, programming macros, custom menus: Dennis: I believe I know what you are looking for - a menu that is on top of everything and can be accessed like a toolbar. There really isn t any way to do that without visual basic programming/macros. I really don t do visual basic questions very...

Excel: Auto Drop-down Menu for Navigation, target value, target address

target value, target address, data validation: It is possible, here is one way of doing it, using data validation. Select the cell that you want to the drop down menu, and go to menu Data- Validation. In the allow box select list , and on the source box you can either type the names of the sheets, or...

Excel: Auto Expanding cells in excel (in a merged cell), format menu, cells

format menu, cells: I didn t contribute to the original question, and indeed I m very sceptical of the answer displayed as I don t think it would work - which is bourne out by your findings! The EASIEST solution is to select the column, the use the format menu, row, autofit...

Excel: Auto Fill a Column of Formulae??, excel function, formulae

excel function, formulae, thanks in advance: I haven t written anything on the subject, but pressing F1 in Excel would bring up the help - which isn t bad on this particular function. Basically INDIRECT will convert text into a reference, so = sheet & row(A1) & !K53 gives =sheet1!K53 which...

Excel: Auto Filling, left mouse button, cell c2

left mouse button, cell c2, cell c3: Hi Sy, Here are a few different ways. 1. You can type in cell C2 4 PM and in cell C3 6 PM then select cell C2 and C3 and then drag the fill handle down. The fill handle is the litte tiny square on the bottom right-hand corner of the selection C2:C3....

Excel: Auto Filling, cell c3, cell c2

cell c3, cell c2, increment: If you want to do with autofill, then put 4pm in one cell (entered as 4:00 pm) and 6pm in the next cell - then select these two cells and drag down - the two cells give Excel the information it needs to work out the progression. IF you want to do it with...

Excel: Auto Filling, cell c3, cell c2

cell c3, cell c2, time format: Hello Sy Huynh In C3 put in the formula =IF(C2= , ,C2+TIMEVALUE( 02:00 )) format the cell to display Time. (format= Cell= Number Tab, select a time value) in C4 =if(C2= , ,C2+TIMEVALUE( 04:00 )) or you can tie it to C3 =IF(C3= , ,C3+TIMEVALUE(...

Excel: Auto Filling, cell c3, cell c2

cell c3, cell c2, time format: If you have exactly this: 4:00 PM then you can enter =C2+1/12 in cell C3 (1/12 = 2/24 = 1 hour) and fill down as far as you want. You may have to reformat. If you have exactly this: 4PM then excel doesn t know this is a time format and you can t fill...

Excel: Auto Filling, cell c3, cell c2

cell c3, cell c2, seires: EXCEL won t do it because it will and does not recognize 4PM, and 6PM, and 8PM, etc. as any kind of logical seires. If you use 4:00 PM it will. To do that, type 6:00 PM in C3 as well. Then select both C2 and C3. Use the fill handle in C3 and drag down...

Excel: Auto Filling, douglas m smith, cell c3

douglas m smith, cell c3, cell c2: Hi, 24 hours in Excel is equivalent to 1, so one hour is 1/24 or 0.416667 and two hours is 2/24 or 1/12 or 0.083333. 4PM is 16/24 or 2/3 or 0.666667. The formula for C3 would be: =C2+1/12 Copy this down to C4 and it add two hours to 6PM too....

Excel: Auto Format Cell Fill / Font Colour, colur, font colour

colur, font colour, add button: Michael, You just need to use Conditional Formatting. So select the cells where you want this to work. Then click Format/Conditional Formatting and enter the values you want in the boxes provided. You will want to click the Add button so that you can specify...

Excel: Auto fill, answer mark, auto fill

answer mark, auto fill, c model: Make a dummy column on the left of your data that contatenates the two values you want to look up then adjust the formula to start with this column =if(And(F3= ,G3= ), Vlookup(F3&G3,Sheet2!A:E,2,False)) then this will lookup the values entered...

Excel: Auto fill cells based on drop down list selection, select tools, drop down list

select tools, drop down list, a3: Hi Rich (I wish I were rich too!) How are you? Please try the following: 1. Type the list you want in a range, e.g. type in A1:A10 A B C D A1 A2 A3 .... 2.Select this range 3. Select Tools - Options 4. Tab Custom lists 5. Press IMPORT Now you...

Excel: Auto fill/populate accross worksheets, lesion, several ways

lesion, several ways, spreadsheet: HI Wendy Yes what you ask is easily done and can ofcourse be achieved in several ways. Are you familar with VBA code if so send me your spreadsheet with some clear instructions of the data you want to copy and autofill and I will do the rest for you....

Excel: Auto fill in, nextpos, delimiter character

nextpos, delimiter character, import text: Getopenfilename does not support that as part of the FileFilter argument. As I understand it, you would have to use the Windows API to achieve that functionality. Another approach would be to use the dir command to get the files and then select the ones...

Excel: Auto formating macro...., rw2, sh2

rw2, sh2, sh1: It can be done, but it will be easier for me to write code over the original file. You can send it to me at m4zapic [at] yahoo [dot] com, and I will take a look at it. Miguel Follow up: This is the code that we finally used, using additional variables...

Excel: Auto Increment, best access systems, auto increment

best access systems, auto increment, copy windows: Charles Here is a little bit of a different approach. First I create a quote number sheet whose only function is to create a new quote number. Then I copy that quote number to the new quote template. Then I automate it by writing a macro that does...

Excel: Auto Menu, cell b1, cell a1

cell b1, cell a1, cell c1: Mark, I would say that you have a couple of options to make this work. If you aren t familiar with VBA programming, then you are likely better off using a VLookup set of formulas. This would change your desired operation in that you would have to either provide...

Excel: Auto number generation, true application, number generation

true application, number generation, invoice number: Jamie, just track it down in the registry and reset it. Or put a limit on it Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim mpFile As String Dim mpValue As Long Static mpReentry As Boolean If Not mpReentry Then...

Excel: Auto numbers, purchase order form, open range

purchase order form, open range, many thanks: More info needed -- you want a particular cell in this workbook to be different (incremented) each time it s opened? If so, assuming that cell is F3...: right-click the excel LOGO (near the File menu), and select View Code. Paste this into the window you...

Excel: Auto Populating, dropdown boxes, drop down boxes

dropdown boxes, drop down boxes, exact code: Hi Kevin, I am assuming that the number is entered into the cell manually (i.e., not by a formula calculation, DDE feed, etc.). This being the case you can use the worksheet s Change event to execute code that populates the dropdown boxes when the cell...

Excel: Auto populate a cell based on input to another, format cells, date stamp

format cells, date stamp, target: Hi Eric, This can easily be accomplished via the use of a Worksheet Change event macro. Here is code that with only slight modification should do what you want: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect([H2:H12], Target)...

Excel: Auto-populate when inserting new row, b25, target

b25, target, cells: You can use a code like this on the worksheet code page: Private Sub Worksheet_Change(ByVal Target As Range) If Cells(Target.Row + 1, 2) = 100 Then Cells(Target.Row, 2) = 100 End If End Sub This will be activated on every change (like...

Excel: Auto populate numbers in Excel, aidan, maximum number

aidan, maximum number, cells: You could probably do this with a macro, or if you know the MAXIMUM number of columns you might be reaching, you could have a formula in each of the cells in the first column to ONLY display the value if their is data in the corresponding column - alternatively,...

Excel: Auto-populating cells after selecting from a combo box, cat squirrel, combo boxes

cat squirrel, combo boxes, data validation: Hi Rich, Yes, this is possible. It is quite easy to auto-populate cells based on selections from a dropdown combobox. No code is required if you just want to use each combobox to provide a list of possible cell entries. If you want to use the combobox selection...

Excel: Auto-populating data in several worksheets, vlookup formula, dob

vlookup formula, dob, important point: One way to simplify the task is to add an ID column to the master sheet (for example, column A), that must be unique for each contact (increasing numbers, for example). With that, in the child sheet, you can copy/type the IDs that you want to show (column...

Excel: Auto-populating information based on selection in list box., automatic population, j11

automatic population, j11, excel spreadsheet: A Forms-listbox (as opposed to ActiveX listbox) can have a source range (like J1:J10) and a cell link (like J11). When you select an item from the list, the link cell contains the INDEX of the selection. So, if you selected the 3rd item, cell J11 would be...

Excel: Auto push info out of Excel to a report, vertical accuracy, gps observation

vertical accuracy, gps observation, horizontal accuracy: Ken The easiest (but a long startup) would be to create a macro that does all the steps you want and then everytime you need to create a report just run the macro. To get your sort criteria though may be a little more difficult depending on how you...

Excel: Auto Resize Cells, alignment tab, format cells

alignment tab, format cells, format painter: Any row whose height was ONCE manually changed will no longer adjust its height by text entry. You should either select an unused/untouched row and use the format painter to copy/paste the formats onto the other rows, OR, select all the cells and double-click...

Excel: Auto Resize Cells, alignment tab, excel 2000

alignment tab, excel 2000, electronic form: Hey Bruce, There are a couple of reasons why the text could not be showing. -Excel can only have a maximum row height of 500 and cell width of 255. If your text exceeds that, then it will not appear -Only 1,024 characters can be displayed in a cell ...

Excel: Auto resizing MSForms dropdown box with respect to longest string, combobox, working solution

combobox, working solution, dropdownlist: This is very odd that you received this reply, as this was my first thought - but I then developed a more accurate working solution which I thought I d posted on AllExperts! My suggestions were (1) it is possible to right click on the left/right/first/last...

Excel: Auto Sum with Categories, cell d2, excel sheet

cell d2, excel sheet, pull down menu: Ah Tan: I have uploaded a sample spreadsheet here: http://home.swbell.net/nate-sus/excel/forahtan.xls I placed this formula in cell D2: =IF($B2=D$1,$C2,0) I then just copied the above formula from cell D2 through the rest of the cells (D2:G6)...

Excel: Auto save as each worksheet as work book, apple orange, copy application

apple orange, copy application: You can try with a macro like this: Sub SaveWorksheets() Dim strPath As String Dim sh As Worksheet strPath = ThisWorkbook.Path For Each sh In ThisWorkbook.Worksheets sh.Copy Application.Workbooks(Application.Workbooks.Count).Close...

Excel: Auto Time, auto time, rng

auto time, rng, target: right click on the shet tab and select view code. In the resulting code module for the sheet, paste in code like this: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim r As Range, r1 As Range Dim cell As Range...

Excel: Auto Time Stamp, time target, correct syntax

time target, correct syntax, auto time: If Target.Column 3 Then Exit Sub If Target.Row 5 Then Exit Sub if isempty(target.offset(0,-1)) _ and isempty(Target.offset(0,-2)) then Target.Offset(0, -1) = Time Target.Offset(0, -2) = Date end if End Sub -- Regards, Tom Ogilvy ...

Excel: Auto update/averaging of cell with date, cell c3, spreadsheet

cell c3, spreadsheet, nate: Dennis: Can you review the spreadsheet here: http://home.swbell.net/nate-sus/excel/monthlyavg.xls I have a formula that calculates the remaining average and will update automatically when you enter the actual income for a particular month. For example,...

Excel: Auto update/averaging of cell with date, formulae, iitbombay

formulae, iitbombay, budget: Dear Dennis, Prima facie, It is possible. Please send me a proposed layout, I will fill in the formulae and send back to you. Try to be explicit in requirements. If necessary use Cell Comments for my understanding. With regards Tushar tsakhalkar@iitbombay.org...

Excel: Auto update/averaging of cell with date, ogilvy, budget

ogilvy, budget: Dennis, sure, you can get a formula like =(12000-sum(A1:A12))/(12-count(A1:A12)) would give you the new monthy average amount if you put the monthly payments consecutively in A1:A12. this would form the basis for a solution. More specific information...

Excel: Auto update of cell data with date, cell b1, comparison number

cell b1, comparison number, vlookup formula: Tan I m not totally sure that I understand what you are trying to do but I will give it a try. I believe you can use the vlookup formula to lookup the period in your table and bring back the appropriate sales value from the table then compare it to...

Excel: Auto update of cell data with date, cell b1, jan dec

cell b1, jan dec, system clock: Dear Tan, I think the question is nlot clear to me. My presumption-- You want to see D1 = B1/C1 and you have some cells blank in B1 & C1 hence you cannot fill all cells in D1. Solution-- You may enter formula- =IF(AND(A2 ,B2 ,C2 ),B2/C2, )...

Excel: Auto update of cell data with date, cell b1, jan dec

cell b1, jan dec, system clock: Why do you have to do it manually? When are the sales figures in column C entered? Can you put a formula in column D, such as: =if(isblank(c1), ,b1/c1) Perhaps that s what you need. Try this related article, too: http://www.officearticles.com/tutorials/an_extreme_tutorial_in_microsoft_excel.htm...

Excel: Auto update of cell with date, remining, value date

remining, value date, tabular format: You may try hard coding the budget value on the code, change the line that reads: lngBudget = Sheets( Sheet2 ).Range( M2 ).Value to lngBudget = 12000 Then ensure that in sheet2 you have dates entered in range A1:L1, starting in Jan-07 and ending in Dec-07....

Excel: Autocomplete in Drop Down List, data validation, blank rows

data validation, blank rows, auto fill: Ann: Yes, this can be done; however, it requires very specific placement of your data-validation dropdown box. I have uploaded a sample here: http://home.swbell.net/nate-sus/excel/longlist.xls Essentially, your list needs to be located directly above...

Excel: Autocomplete Repetitive Lists, number values, john doe

number values, john doe, colums: Hi John How are you? You try the following: 1. Type the 3 characters of the name, the remainder characters of the name and phone number of the 50 people in A1:C50 of sheet 2 2. On sheet1, type for example, in B2 =A2&VLOOKUP(A2;Sheet2!$A$1:$C$3;2;FALSE)...

Excel: Autocopy results from checkbox to another worksheet, source sheet, cell addresses

source sheet, cell addresses, checkbox: 1 - what do you mean by form creation? A VBA-type User form? 2 - result of a checkbox is true/false; result of a dropdown is the contents of the dropdown. Is this what you want? FYI, it would be done by a worksheet_Change event in code unless you can simply...

Excel: AutoFilter not applying, set operators, filter selection

set operators, filter selection, string set: Adam, Your problem is related to the fact that excel VBA uses US Date conventions (mm/dd/yyyy) and you are using dd/mm/yyyy. I can t test this work around, because I am in the US and your code would work fine for me, but I believe this will work for you:...

Excel: AutoFilter criteria realtime date scenario, todays date, daily basis

todays date, daily basis, format date: The recorded macro should have give you this but assuming the date you are filtering on is in the third column of the data being filtered then: Dim Todays_Date as String Todays_Date = Format(Date, mm/dd/yyyy ) Range( A1:D10 ).AutoFilter Field:=3, Criteria1:=Todays_Date...

Excel: AutoFilter, macro button, autofilter

macro button, autofilter, dialog box: The autofilter button (your macro button, I assume) should first unprotect the workbook, supplying the password, then reprotect when it s finished. Restoring the password would only work if it also saves the workbook! I would recommend emailing me the workbook...

Excel: AutoFilters and Macros, text labels, dynamic values

text labels, dynamic values, data layout: Ron, I don t understand your data layout. It is confusing whether you have a list of names and the second column will be A or B or C or you have a list of names and then 3 additional columns with text labels and then a column with some value you want to sum....

Excel: Autofill for blanks, sames, autofill

sames, autofill, bottom right: Sorry, typically I do not write macros for people on this site who seem to think I am expected to do so, who need me to do so, and/or who ask me to do so. 99% of all macros requests are more involved than you first think and therefore take longer to...

Excel: Autofill hyperlinks, visual basic for applications, cell reference

visual basic for applications, cell reference, location text: The only way I know of to automate this sort of process is to use Visual Basic for Applications (vba) coding. If you understand the basics of vba, then you can automate this task by using code like: Sub makeHyperlinks() Dim linkCell As Range Dim...

Excel: Autofill of other worksheets from master, whole enchilada, exact formula

whole enchilada, exact formula, autofill: Hi, Patrick! I am so sorry for the delay in my response. I was over a thousand miles away from my PC. :) I hope that you have gotten an answer by now, but just in case... Likely... copy another cell such as A4 and paste it to A2. That should make any...

Excel: Autofill Workseet, chip pearson, address fields

chip pearson, address fields, viable solution: Swiftee, I think you would need an event macro to do this. You could use the change event to detect the entry of the word yes. However there is a lot of ambiguity in your description. A column named Name could mean you have a defined name or it just has...

Excel: Autofilter with 2 header rows, column headings, header rows

column headings, header rows, autofilter: Again, what is your question? If you want to know how you can do this (all you say is what you need) then you can t have two rows of column headings and make the code work and have it provide relevant data. If you want to maintain a 2 row column header...

Excel: Autofilter active even the sheet is protected!, sheet menu, filter method

sheet menu, filter method, excel2000: If you have to use AutoFilter, you are basically stuck as there is no way of using AutoFilter with this version (that I know of) WITH protection enabled. However, an array filter method WOULD work - a basic example of this is at http://www.aidanheritage.byethost3.com/excel/Array_Filter.xl...

Excel: Autofilter - Excel 2003, autofilter, pivot tables

autofilter, pivot tables, aidan: Difficult to answer from words alone BUT I THINK you have formulas referring to ALL the data on the same line as SOME of the data - you would be best to have these formulas on other pages, or above/below the data. You might also like to look at the data menu...

Excel: Autofilter Macro question, john walkenbach, heather heather

john walkenbach, heather heather, vba access: Heather, There is no vba access to the list built for the autofilter dropdown. You would need build a list of unique elements in your key column, then loop through that list and set the filter to each element sequentially - processing the resulting filtered...

Excel: Autofilter - Macro, cell a1, macro tools

cell a1, macro tools, try and error: You are welcome! Regarding learning macros, I learned with a book of VBA for Excel 97 and a lot of try-and-error with the record macro, but now there are much more resources on the Internet and the book part is optional (I think that the try-and-error...

Excel: Autofilter; Saving values as separate files, lengthy macro, excel spreadsheet

lengthy macro, excel spreadsheet, pivot table: Looks like you could to this with a pivot table with the client as a page field. If that s true, then you could use the builtin feature of Show Pages which will put each client on its own page with its own data and you re practically done. The bottom menu...

Excel: Autofilter, selecting the appropriate row, rate swap, loop through

rate swap, loop through, field 3: dont do it that way! loop through ALL records and TEST the record for the criteria - you can then return the row number from that record something like this would do it Sub qt() For Each ct In Range( a:a ) could have a better test for the range...

Excel: Autofilter, sum formula, t effect

sum formula, t effect, pivot tables: if you are using the SUBTOTAL function, this should ignore any other subtotal functions in the range, so it should resolve itself - My own preference would be to have the summary data away from the rest of the data - either above, to the side, or on a different...

Excel: Automactic different font color, cell interior, different colors

cell interior, different colors, colorize: Hannah I don t know of an automatic way to do this in Excel. It could be done using VBA, however, and this code would do that for you: Sub Colorize() For Each cell In Selection If cell.Formula Like =* Then With cell.Interior .ColorIndex...

Excel: Automaitically update, microsoft office 2000, using microsoft office 2000

microsoft office 2000, using microsoft office 2000, cell b1: There needs to be a cell (column) with a date in it to know when it s within 15 days. If that s column B, say, and it contains March 24, 2004 (15 days from now), here s what to do: Select column B, use Format/Conditional Formatting, change Cell Value Is...

Excel: Automate an account reconciliation, account reconciliation, data cells

account reconciliation, data cells, cell a1: Devyn Actually, it s separate. I would remove all subtotals prior to doing anything else. Then add the new data, resort, remove all zero entries, and then subtotal. The code that I gave you needs to be inserted into whatever code you already have. Jef...

Excel: Automate External Data Input into Excel, html document object, google maps

html document object, google maps, google: There are a few ways, but it all depends on what format the supplier published the information in their web page and also on the version of Excel you are using. If Excel 2003+ and the supplier published the lists in XML format, there is VBA code that can...

Excel: Automate Hide-unhide column, control button, google

control button, google, password protection: Hi Rodrigo Ok no problem I should be able to do this for you in a very short time. I will need a none confidential version of your spreadsheet by email with a note on which fields you wish to hide. limbic_lesion@hotmail.com Regards Des ...

Excel: Automate time stamp and calucate time taken details, time stamp, lesion

time stamp, lesion, busines: Hi Ankit Thanks for your question. I m away on busines till the weekend so Im going to consioder your question and tyhen get back to you then. If would help if I had a copy of the spreadsheet ( or a non confidential version) to work with. limbic_lesion@hotmail.com...

Excel: Automated range selection in lookup table, cell d1, cash forecasts

cell d1, cash forecasts, range selection: Though it requires a additional column, I suggest following. ASSUMPTION: Range A2:A29 contains dates (1-Feb to 28-Feb). Range B2:B29 contains Amounts Cell D1 contains the desired date (say Feb10,2007) In Cell C3 enter formula - =A2 =$D$1 Copy the...

Excel: Automated Time Stamp, douglas m smith, keyboard shortcut

douglas m smith, keyboard shortcut, sheet tab: Hi Keri, There s a keyboard shortcut that enter the current time in a cell: Control Shift : For completeness, this shortcut enters the date in a cell: Control ; If you want to get fancy, you can totally automate the process by attaching a macro...

Excel: Automatic Cell Color, format font, font change

format font, font change, cell a1: Here s an example. On your spreadsheet, select cols B and C. (You could choose more; we re just selecting 2 to make this example simple.) From the main menu, select: Format, Conditional Formatting, [Condition 1], Formula Is, =(B1 A1), Format, Font, Color,...

Excel: Automatic Chart Update - Date Based, test case automation, automation area

test case automation, automation area, spreadsheet table: A simple way of doing it with formulas can be using the following one in the table 2, for the current and future weeks: =IF(WEEKNUM(TODAY())=WEEKNUM(A5),Num_Autom, ) Where Num_Autom is the range where the sum of Table 1 is (if it is dynamic, we can use...

Excel: Automatic calculation, mail group, intervals

mail group, intervals, xl: It s on my website... www.XL-Logic.com Has nothing to do with my mail group, but thanks for stopping by. It s in the download section on my website with all the other example files I keep there. Look for it here: http://www.xl-logic.com/pages/vba.html...

Excel: Automatic calculation, code yields, cell a1

code yields, cell a1, doevents: The use of GetTickCount was to put in a delay - so you need a number equivalent to 15 seconds - this bit of code should do NOTHING - so Private Sub CommandButton1_Click() Dim HoldVar As Double dim loopvar as double loopVar = GetTickCount + 15000000000000#...

Excel: Automatic calculation, open application, syntax

open application, syntax, automatic calculation: right-click the excel LOGO near the file menu, select View code, enter this: Private Sub Workbook_BeforeClose(Cancel As Boolean) NoMore = True End Sub Private Sub Workbook_Open() Application.OnTime Now, Every15 End Sub In a regular module...

Excel: Automatic cell numbering when opening worksheet, sheet1, vba

sheet1, vba, increment: You can t have Excel wihtout VBA, so you do have it. Let s say it s cell E2 in sheet1 that you want to increment. Open this file, right-click the Excel LOGO near the file menu, select View Code, put this code in: Private Sub Workbook_Open() With Sheets(...

Excel: Automatic Data transfer between worksheets, time don, drop down menu

time don, drop down menu, right direction: Sorry, as politely as possible, I don t write macros for people on this site who need one, want 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 site would. 99% of all macros...

Excel: Automatic document numbering, excel sheet, automatic document

excel sheet, automatic document, pono: With the template active, right-click the Excel LOGO (near the file menu) and select View Code. Paste this in. You didn t say where you wanted this PO to go, but you can change the code accodringly -- I m putting it in cell G1, and I have it set to start with...

Excel: Automatic entry of an "X" with a mouse click, multiple choice test, douglas m smith

multiple choice test, douglas m smith, excel tricks: Hi Jim, Start by building a table of the cities and their corresponding regions. Then, you can read this table with a VLOOKUP formula: =VLOOKUP(A2,$R$1:$S$100,2,FALSE) where A2 is the city name you want to look up and R1:S100 is the city/region table....

Excel: Automatic expansion of cells when typing in text, column width, ed ed

column width, ed ed, ogilvy: Ed, The only way would be to use a macro, but this would have to be after the fact since macros don t run while you are in edit mode (while you are typing). Even a macro would just use the Autofit command. I am not sure I have ever seen it fail if wraptext...

Excel: Automatic links?, microsoft site, assembly instructions

microsoft site, assembly instructions, many thanks: Please excuse the delay, Andrew. I was busy over the weekend, and wanted to do a little research on this. But the good news is that I found what I was looking for. I had heard about a function to do just this, and found it on the Microsoft site: http://support.microsoft.com:80/support/kb/articles/q188/4/49.asp&NoWebContent=1...

Excel: Automatic Macro, shift key, collegue

shift key, collegue, macros: Open the file (preferably from the last used file menu) but hold down the shift key -this will disable the macros from running BUT still allow you to edit/view the macros. IF you want to permanently stop them, you can do this from the tools options general...

Excel: Automatic print area, upper bound, office xp

upper bound, office xp, spreadsheet: Georges, I may be oversimplifying your problem, but as I read your message the below macro will do what you ask. Were you looking to dynamically set the upper bound of column F, meaning the upper bound of F is dependant on the upper bound of A, or will...

Excel: Automatic process in Excel, target range, ax1

target range, ax1, message box: Make sure you have the macro in the right place. IF AX1:AX500 is referring to sheet1 then the code should be attached to sheet1 and not in a regular module to make it run automatically. In the VBE, double click the sheet. sheet1 for example, where AX1:AX500...

Excel: Automatic row resize for additional data, format cells, warm regards

format cells, warm regards, alignment: Dear Steve, I hope that, Wrap Text function will help you to resolve your problem. When you use wrap text function it will resize the height of the particular row but not the column. To enable the function follow the instructions given below. ...

Excel: Automatic time and date stamps, douglas m smith, custom dialog box

douglas m smith, custom dialog box, number signs: Hi Phil, Yes, Excel can do this. Select Page Setup... from the File menu. In the dialog box, click the Header/Footer tab. In the center of the dialog box are two buttons, Custom Header... and Custom Footer... . Click the one where you want to...

Excel: Automatic Value cell change, cell b6, vlookup function

cell b6, vlookup function, autocorrect feature: Hello, I don t think there is a quick and simple way of getting this scenario to work. Once you replace the Desk text with something else, the original Desk entry no longer exists. One thing you could try is to use the VLookup function to check the...

Excel: Automatic Value cell change, vlookup in excel, word chair

vlookup in excel, word chair, vlookup formula: Hi, Armando, I will be unavailable for the next few days, and I don t want to make you wait until I get back. I suggest you send this message to either Tom Ogilvy or Bob Umlas, both wonderful experts on this site. They can undoubtedly answer you right...

Excel: Automatic Value cell change, autocorrect feature, aidan

autocorrect feature, aidan, accedes: If you want the value to appear in the SAME cell you are going to have problems - VLOOKUP can be used to display the result in another cell, and I guess a macro could be made which does the job for you - possibly on CHANGE in the cell - if you can send me...

Excel: Automatic Value cell change, autocorrect feature, allexperts

autocorrect feature, allexperts, ogilvy: Send the file to twogilvy@msn.com in the subject, start with AllExperts: or it will likely get deleted since I get a lot of spam and delete unrecognized from addresses/subjects. I anticipate a macro approach, so when I return it you will probably...

Excel: Automatic value cell change, sorry jeff, french table

sorry jeff, french table, sheet1: Armando, I don t like to give answers like this one, but I don t believe that what you want to do is possible. Excels method of finding translations is to use the research service. The results of searches in Research are not something that can be retrieved...

Excel: Automatic value cell change, vlookup function, french table

vlookup function, french table, sheet1: Hi, Armando, (This question sounds familiar. Have you sent it before?) If I m understanding correctly, it sounds like the VLOOKUP function will do what you want. Read up on it in Help to see how it works. If you re still having problems, you can send...

Excel: Automatic value cell change, french column, vlookup function

french column, vlookup function, french table: I think I have already answered this question. Regardless, setup a table with each column in the table representing the same word in a different language. Then use the VLOOKUP function. For example, assume your table is in D1:G4 and you type the word...

Excel: Automatic value cell change, french table, sheet1

french table, sheet1, spreadsheet: Armando Based on my understanding of the question you should be able to use the replace feature of Excel.. Go to toolbar/edit/replace Type in the word you want excel to find, and the word that you want to replace it with. Clic replace all , or if...

Excel: Automatically changing data because of the date, hewlett packard pc, hardware spec

hewlett packard pc, hardware spec, fourth column: Hi, Ian, The critical date that is needed here is the first day of the coming month. We can find that easily by using a combination of date functions: DATE(year,month,day) Returns a date. YEAR(date) MONTH(date) DAY(date) These extract only the...

Excel: Automatically envoking a Macro in excel, macro in excel, autosys

macro in excel, autosys, parameter line: Hello, Rodney, Excel does indeed include automatically-executing macros. There are many different events to which you can attach a macro, and opening up a workbook is the most common among them. To make a macro run automatically when the workbook is...

Excel: Automatically populating fields, data validation, vlookup

data validation, vlookup, consultant name: Sounds like a VLOOKUP/Hlookup(depending upon how the data is laid out) question - the item to be looked up is the consultant, and this would generate the appropriate values - if you want to email me the file I can give you specifics for vlookup, but basically...

Excel: Automatically shade the last row of a series, conditional formatting in excel, excel help

conditional formatting in excel, excel help, wise one: you still do not have to use a macro. conditional formating works regardless of the number of conditions. if you use the Cell Value Is obviously your use of it is limited. Instead, use the formula option. use an OR / AND formula, something along the...

Excel: Automating change in stock of component, combo boxes, componenets

combo boxes, componenets, component type: NewGuy, Yes you can do it but without more detail or seeing how your workbook is set up, I cannot provide specific instructions over this medium. If you send me your file or a reasonable sample, I will be happy to take a look at it and give you some pointers....

Excel: Automating change in stock of component, combo boxes, componenets

combo boxes, componenets, component type: Sounds like a conflict - if you want the stock descreased when a component is selected, that conflicts with the statement that yu want the stock being updated by pressing a button, so I m confused. In either case you will need VBA to do it. In the former,...

Excel: Automating daylight saving, eric eric, time time

eric eric, time time, excel 2000: Eric, it is possible to have VBA code change the system time for you. Actually, the code is very simple, as follows: Time = Time + 0.0416666666 Automating that to happen on those dates will be more interesting, however. Consider the possibilities. If you...

Excel: Automating Excel, line functions, blank chart

line functions, blank chart, dbase format: There is no DOS command line command you can run to automate this UNLESS you write an entire executable program and run THAT from the command line. EXCEL will do all of this for you at the click of a button but the code will have to written that runs when...

Excel: Automating Numbers, comma seperated, csv file

comma seperated, csv file, comma delimited: Michael - You want to use the Fill Handle pointer. Follow these instructions: Type the number 1000 in cell A1 Type the number 1001 in cell B1 Select both cells A1 & B1 Notice that there is a little black box at the lower right hand corner of the...

Excel: Automating protect/unprotect of worksheets, check boxes, default values

check boxes, default values, macros: Set to True the things you want a user to be able to do when the sheet is protected. Sub Protect() For Each Worksheet In Worksheets Worksheet.Protect Password:= password1 , _ DrawingObjects:=True, _ Contents:=True, Scenarios:=True, _ AllowFormattingCells:=True,...

Excel: Automating an Select and Copy Process, date az, variable state

date az, variable state, cell a2: Priyanka, I don t know for sure, but the code below should do the trick for you. It starts on Sheet1 (so change the code to match the names of your sheets if necessary), cell A2. It sets the variable State to the value of that cell. It then selects the...

Excel: Automation of a combo box change, mydocument, combo boxes

mydocument, combo boxes, vba code: Dan, I don t have good news for you. I don t know if that property can be changed via VBA code. I can get Excel to recognize each of the objects (comboboxes) but I can t figure out how to adjust that specific parameter. This code: Set myDocument = Worksheets(1)...

Excel: Automation of formatting?, capabilities of microsoft excel, ns dept

capabilities of microsoft excel, ns dept, automation project: Kevin, the data would have to be imported into the document template for that to work, but you would have issues with the protected cells when importing. I would say that you need to use VBA code to set the formats of the cells in question. Since those...

Excel: Automation, mainframe system, ms query

mainframe system, ms query, learning curve: Mark, the Add-in is certainly a fine option. It s very useful because it requires less knowledge to operate than building your own system in VBA to extract the data you want. That of course is the other main option, VBA. Which is best is relative. If you...

Excel: Autonumbering between named rows, target range, md1

target range, md1, numbering system: Hi Jonathan, Here is a modification to your code that I believe accomplishes what you desire: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range(Range( CommSystem ).Offset(1), Range( ATS ))) Is Nothing Then ...

Excel: Autosave files not there, microsoft technet office, office developer center

microsoft technet office, office developer center, microsoft excel 2002: Thanks for the additional information - here is the article you need to refer to but BASICALLY the autosave is there for document protection, and is overwritten by your actions - so saving and closing a file would remove the autosaved version - autosave exists...

Excel: Autosave, bringer of bad tidings, third party software

bringer of bad tidings, third party software, security reason: That rather reduces the options - almost (but not quite) to NO YOU CAN T DO IT - the ONLY possible alternative I came up in my research was a shareware program at http://www.eldos.org/autosavexl/autosavexl.html - however, even this doesn t seem to do what...

Excel: Autosizing of cell, long sentence, cheers

long sentence, cheers, cells: Hi Steve I hope you are well. I am sorry but I do not know how to do it. I ve even tried to write a vba program but it does the same. It works for wrapping the cell but not together with the merging cells formatting. I am sorry I cannot help now. I ll try...

Excel: AVERAGE Help, possitive, google

possitive, google, m40: The solution that I usually use may not work in this case, as it needs contiguos ranges. It uses SUMIF and COUNTIF, something like: =SUMIF(A1:A7, 0 )/COUNTIF(A1:A7, 0 ) It won t work in your current scenario, but it can give you ideas for other cases....

Excel: AVERAGEIF() function, array formula, golden numbers

array formula, golden numbers, perfect solution: There isn t a perfect solution I can think of that fits your description; however, I will offer the following ideas: 1) There is a formula called SUBTOTAL that includes an average function. If your data is organized in rows (unfortunately, yours is in...

Excel: Average with condition, a24, null return

a24, null return, a23: Not clear what your two cells are doing - if these are formulas, fix the formula so that return for a null return. If values, then simply deleting the value should mean the average works fine. (the average of 12.50% on it s own is still 12.50%) if...

Excel: Average (excel 2000), array formula, ogilvy

array formula, ogilvy, excel 2000: One way it so use sumif and countif =sumif($A$1:$A$200, STASCO ,$E$1:$E$200)/countif($A$1:$A$200, STASCO ) another would be to use an array formula =Average(if($A$1:$A$200= STASCO ,$E$1,$E$200)) entered with Ctrl+Shift+Enter if you then reselected...

Excel: Average Formula, countif, xls

countif, xls, months of the year: Are you using something like =SUM([Norm.xls]Sheet1!$A$2:$L$2)/12 or =AVERAGE([Norm.xls]Sheet1!$A$2:$L$2) ? The latter should work fine. If it doesn t, try this: =SUMIF([Norm.xls]Sheet1!$A$2:$L$2, 0 ,[Norm.xls]Sheet1!$A$2:$L$2)/COUNTIF([Norm.xls]Sheet1!$A$2:$L$2,...

Excel: Average Formula, countif, c10

countif, c10, excel 2000: Hi Steve How are you? The simplest i can suggest you is the following Assuming you have in A1:A9 1,2,3, 0,4,5,6,7,8 1st Sum up the numbers A10: Sum(A1:A9) 2nd Count the numbers 0 B10: =COUNTIF(A1:A9, 0 ) C10 Computing Average =A10/B10...

Excel: Average Forumla, countif, steve thanks

countif, steve thanks, zero values: Hi, Steve, thanks for writing, I m happy to help you. Your first question says that you want to average a series of numbers, excluding any zero values. The other expert does that for you, but requires that you use three different cells to do so. Perhaps...

Excel: Average formula coming out wrong, empty cells, countif

empty cells, countif, excel workbook: Why doesn t =AVERAGE(A2:A12) work for you? This should not count empty cells. Anyway, try this... =IF(ISERROR(SUMIF(A2:A12, 0 )/COUNTIF(A2:A12, 0 )), ,SUMIF(A2:A12, 0 )/COUNTIF(A2:A12, 0 )) It assumes that A2 through A12 is your range of cells...

Excel: Average, Min, MAx, and Standard Deviation Graphing, absolute references, dollar signs

absolute references, dollar signs, standard deviation: Hi Petra, The easiest way to do this is to create a column for Average, Min, Max, and Standard Deviation and instead of one cell holding the Average value, you copy and paste the value or formula down the whole column to match the amount of data that you...

Excel: Average, hi amy, excel formula

hi amy, excel formula, loan amounts: Hi Amy, Here is an easy way to make any Excel formula see only the cells that are visible. This means that cells that are hidden due to filtering will not be included in the formula. 1. Incorporate this user-defined function into your workbook: ...

Excel: AverageIf, e47, sarfraz

e47, sarfraz, cells: Sarfraz: There might be a better way to do this, but I know the following will work: =SUM(IF(E29 =20,E29),IF(E35 =20,E35),IF(E41 =20,E41),IF(E47 =20,E47))/SUM(IF(E29 =20,1),IF(E35 =20,1),IF(E41 =20,1),IF(E47 =20,1)) Please let me know if this isn...

Excel: AverageIf in multiple cells, e41, continuous range

e41, continuous range, excel 2002: Sarfraz: I ll answer your question first and then I ll explain the original answer and how it works: =AVERAGEIF(E29,E35,E41,E47, 20 ) NOTE = AVERAGEIF only exists in Excel 2007...you mention Excel 2002...so I m slightly confused...unless you are...

Excel: AverageIf, countif, sumif

countif, sumif, manual car: Ok, in that case you can SUMPRODUCT instead of COUNTIF, something like: =SUMIF($N$2:$FO$2, man ,N35:FO35)/SUMPRODUCT(($N$2:$FO$2= man )*(N35:FO35 0)) The SUMPRODUCT part will count each column that have man in the second row, and the value on row 35 is...

Excel: Averages, simple matter, a2

simple matter, a2, percentages: Hello, Luke, Is each of these values in a cell by itself? I ll assume so for the ease of this answer. (If not, you can write me back.) Let s say that your values are in row 2. They start in A2, and they might possibly go as far as CA2. Let s also say...

Excel: Averages, a1, wb

a1, wb, yahoo: Make these changes: =AVERAGE(OFFSET(B14,0,COUNT(14:14)-3,1,3)) =AVERAGE(OFFSET(B14,0,COUNT(14:14)-5,1,5)) =AVERAGE(OFFSET(B14,0,COUNT(14:14)-10,1,10)) =AVERAGE(OFFSET(B14,0,COUNT(14:14)-20,1,20)) if this doesn t work, then email me the wb - bobumlas@yahoo.com...