Hi Ashley, I would use one table that holds all data of the employees. It is this table that is kept up-to-date. Then use one single worksheet, which has a dropdown from which to pick an employee (or an employee number). That worksheet is properly formatted...

Excel: Excel Analysis Tools - Sampling

Hi Don, Try recording this manual action (you going to analysis tools and click on sampling) in a macro through record macro button, then assign a shortcut key to it. Now, you can perform the full function using the shortcut key only. Hope this helps,...

Excel: Excel - Return all occurrences

Sean, If you want to find a second occurrence then =A1& &MID(A2,FIND( Req ,A2,FIND( Req ,A2)+4)+7,3) in A1 I have 1234 in A2 I have the test string you show in C2 I have =A1& &MID(A2,FIND( Req ,A2)+7,3) in C3 I have =A1& &MID(A2,FIND(...

Excel: Filter on pivot table data field

David, You can send the workbook to twogilvy@msn.com Make sure it has some sample data in it that can be used to create the pivottable - in otherwords, if I run the macro it should create the same pivottable you are looking at and then I can see what...

Excel: force/control number/dashes format

I m slightly confused as you refer to numbers with a custom format, and then to dashes - IF the numbers ARE numbers with a standard format, then the only bit to check is the position of the 8. But I m guessing you need the data entered as text - if so, using...

Excel: function for font style

click on the B column header to select the entire column B. From Excel Menu, choose Home, Conditional Formatting, Use a formula to determine which cells to format, Format cells where this value is true: =A1, Format, Fill, [yellow], OK, OK. Type any text...

Excel: Help reqd on vlookup formula

Abhimanyu, 54053830 is 8 characters wide and starts in the 6th character of the string in A9. =INDEX($B:$B,MATCH( * &MID($E2,6,8)& * ,$A:$A,0),1) So this formula in F2 worked for me as I understand the requirement. -- Regards, Tom Ogilvy...

Excel: highlighting cell which is not linked to other dependent cell

Hi Rujan, This macro selects all cells on the current sheet which have no dependent cells anywhere: Sub HighlightNonLinked() Dim oRng As Range Dim oCell As Range Dim lShpCt As Long lShpCt = 0 On Error Resume Next For Each...

Excel: Identifying formulas

You need some VBA. In a module, write this: Function HasFml(rg As Range) As Boolean HasFml = rg.HasFormula End Function Then select all the cells you want this to apply the CF to, and assuming the ACTIVE cell is H10, this is the conditional formatting...

Excel: Link Files

I m not 100% sure what issue you are describing here, but it sounds as though it may relate to the type of reference in the link - as long as the folder is networked so you both have access to it, you should be able to resolve the problem - see https://www.google.co.uk/search?q=excel+links+relative+path&sourceid=ie7&rls=com.microsoft:en-gb:IE-SearchBox&ie=&oe=&safe=images&gfe_rd=cr&ei=3v0iU7ayJ6HR8geIv4Eg...

Excel: Linking Range of Cells from Different Workbooks

Chris Its a bit difficult to tell how to do it in an easier way without knowing the details of your workbooks, but here are a few suggestions. If your data looks the same all of the time, I.E. it is in the same row and columns and takes up the same number...

Excel: Linking Text Boxes in Excel Form

Put this code in the Userform code - assumes your textboxes have not been given special names - works for me... Dim bef, aft, n As Integer, T2 As String Private Sub CommandButton1_Click() On Error Resume Next n = InStr(aft, bef) T2 = Me.TextBox2.Text...

Excel: macro to delete obsolete product specs

Sub Deleter() assumes product# is in column B; key for deletion is entered in A1 Also assumes column J is available for temp use 0 if not, change that in the indicated code Application.ScreenUpdating = False n = Range( B1048576 ).End(xlUp).Row...

Excel: Pivot Tables

Hi Karen, I m not aware of a naming for pivot tables, however I have another solution. Everytime you add a new year, create the pivot in a new sheet instead of the same sheet. Now, in your formula, let the sheet name be linked with another cell. Everytime...

Excel: Relating data in two tables

Make sure Table 2 is off to the right, not below the original data, so there are no shared columns. Then use the SUMIF() function to create your sums. Press F1 and read the Excel help files for some great examples and explanation of ways to use that function...

Excel: Relating two tables

Hi Hitesh, This looks like a straightforward linking problem. If I understand your question correctly, you just need to add a sum formula and link table 2 to that total. I don t really see a need for macro or pivot table Hope this helps, Gulshan...

Excel: roll up many sheets to a master sheet

Here is what you could do: - Open one of the form files - Open your reporting workbook - On a new worksheet, one single row, create formulas pointing directly to the cells where data has been entered into (one cell per cell, just type the equal sign,...

Excel: Sumif

Ahmed Ezzat, first, my answer will be for US English Excel since that is what I use and know. If you don t use that, you will have to make the necessary adjustments for your regional settings/version. Assume sheetname: Sheet1 Col A Col B ...

Excel: Taking attendance & keeping track of votes of a group using a barcode scanner

Warren, Isn t that what I said: Nonetheless, if you want to send me a sample workbook and directions on what you want, I can adapt that code to your particular situation. twogilvy@msn.com so to be clear - yes, email the excel sheet with instructions...

Excel: Transpose action macro with same format of cell

Rakesh, see if this is what you want: Sub abc() Dim sh As Worksheet Dim sh1 As Worksheet Dim rCity As Range, rCode As Range Dim cell As Range, cell1 As Range Dim rw As Long Set sh = Worksheets( Sheet1 ) Set rCity = sh.Range( A2 , sh.Range( A2...

Excel: VBA - Change number, then print

Alison, Dim ans as String, lnum as Long, i as long ans = InputBox( Enter Number of trip sheets ) if not isnumeric(ans) then exit sub lnum = clng(ans) if lnum 50 then exit sub set some limit? for i = 1 to lnum put code in here that will do each...

Excel: vba solution to partial data entry into cell range

Hi, Put this code in a normal module: Option Explicit Function TestData(oSh As Worksheet) Dim oCurData As Range Dim oBlanks As Range Dim oCell As Range Dim bFoundEmpty As Boolean On Error Resume Next For Each oCell In...

Excel: vlookup/offset

Nawa Raj - The files you upload come to me as .pdf - I cannot input formulas and return them to you. But looking at your files, I m not sure you want to use VLOOKUP(). If the purpose of your master sheet is to enter all your information on one page, but...

Excel: assigning random numerical values ot cells in excel

Hi Chris How are you? you can either use =rnd() which generates a random number between 0 and 1 or =randbetween(n1, n2) which generates a random number between n1 and n2 to sort them up , you can use =largest( range, k) where...

Excel: Automatic Open Hyperlink in excel

Hi Saurabh, Excel normally opens only when user clicks on the hyperlink. However, if you are comfortable with macros, you could write a small macro to open the hyperlink everytime you enter the value in the cell. If you are not too find of macros, then,...

Excel: Automatically Open Hyperlink

1) 123.pdf is not a full address to a file, it s the name of a file. If you want clickable hyperlinks I m assuming your lookup table actually has the full path to the files in question. 2) So, assuming you want to follow the hyperlink in cell D6 anytime...

Excel: calculating days between dates and subtracting a raw number

The formula had been entered as an array formula (ctrl shift enter rather than just enter), which it didn’t need to be – all formulas should be normal formulas in this case. NOTE also that your use of the datevalue function is fine as long as all users have...

Excel: CBA code to pick up a file and email it automatically

The problem is with the file address The correct code is: Sub SendMail() Dim OutApp As Object Dim OutMail As Object Dim Filename As Variant Dim Path As String Dim Todaysdate As String ...

Excel: Charts & Graphs

Kenneth, I happen to teach basic concepts of statistics and we do a section on graphs - but it is only part of a chapter. Basically more about how charts can misrepresent the facts so that we understand that and can be more discerning examining the obvious...

Excel: Conditional Formating

My solution works fine. Not sure what you did. I m also not sure if what you said is a question or not: That did not work either, so I think I am going to go another route. I want to conditional format by a specific date. I would like column F to Highlight...

Excel: Conditional Formatting with VLookup?

First, there should be no quotes in the formula you gave. Next, the character G SHOULD have quotes: =IF(VLOOKUP($A$5,TEAMS!$K:$O,5,0)= G ,1,0) Then apply the conditional formatting based on 1 or 0. However, you could also have just done the VLOOKUP: ...

Excel: Copy from data from one workbook to another

Sub REPORTING() Application.ScreenUpdating = False Application.StatusBar = Job Register Daily Report is currently updating, please wait. COPY ALL REPORT ITEMS TO REPORT SHEET Workbooks.Add Sheets(1).Name = REPORT ...

Excel: Count non blank row from a particular row

No macro needed, this can be done with an array formula: =MIN(IF($A$4:$A$100= , ROW($A$4:$A$100), ))-4 ...confirm that formula in a cell by pressing CTRL-SHIFT-ENTER to activate the array. You will know the array is active by the curly braces {...

Excel: Count if two variables are met

Hi John, The combination of names and LPN s and such makes this a hard puzzle to solve. I inserted an extra column B in which I put the LPN and RN denotations. Then this formula solves your problem: =SUMPRODUCT(((($B4:$B86= LPN )+($B4:$B86= RN ))...

Excel: datevalue

Nahid, when I put 18/3/2014 in a O3 as a string, then I put your formula in and check how the argument is being constructed, I get 14//2/18/3 so you can see that you formula is not correct. If I want to do mm/d/yyyy with your formula I would...

Excel: eccel

Hello, please upload the file from http://programarexcel.com/p/contacto.html I check the file and give it back with the solution Hi I do not understand what you need to do exactly, please upload the file again and put example of what you want, put manually...

Excel: Exact formula

with the data being in a1 to D6, and John being in row 5, the formula would be =IF(OR(ISBLANK(B5),ISBLANK(C5)), ,B5=C5) (I ve given you the formula for this row, but obviously it can be extended to all other cells in the row. I ve assumed the test...

Excel: exc 2007

something like this should work for you I think Dim Checkvar As String, ExitLoop As Boolean, MyR As Range If WorksheetFunction.CountIf(Range( A:A ), Range( C1 ).Value) 0 Then Checkvar = InputBox( Please enter the vendor code ) repeatsearch:...

Excel: excel 2007 help

Okay Saurabh, Here s another possible solution: I understand that part numbers can possibly repeat across different vendors. If they repeat, you need to look at the vendor name else only the part number. Assuming your part numbers are stored in column...

Excel: Excel

There s no need to select cells once you ve FOUND them using the .FIND method in VBA. You ve stored that cell s location so you can send commands to it directly or to nearby cells. Assuming column B includes the FULL path to these files including the...

Excel: Excel

Assuming the vendor name is in column C, you could do something like this: Sub macro() Dim myR As Range Dim myPrevR As Range Dim sVendor As String Set myR = Me.Range( A:A ).Find( _ What:=Me.Range( C1 ).Value, LookIn:=xlFormulas)...

Excel: Excel

Hi Diego, Instead of trying to populate values in second sheet, I ll suggest you have a formula in second sheet to pick up values from first sheet. In second sheet, you can create an IF formula --- if c1 in sheet 1 is acn1, then b1, else null. And so on....

Excel: Excel BAr Code

Saurabh: Excel doesn t natively support bar codes; however, there are a variety of barcode system fonts that will work in Excel. Basically, if you have a cell with a valid number in it... you can apply the format of that cell to a barcode font and it...

Excel: Excel and barcode scanner

Michael, First, I don t own any barcode scanners and never have. I have talked to a few people that have used them so I understand how they have been used in some instances. Basically, for the systems I have knowledge of, the barcode scanner acts as...

Excel: Excel complicated formula condition

I m pretty sure this ARRAY formula will do what you want: =INDEX($B$9:$B$13, SMALL(IF($P$9:$P$13 0, ROW($P$9:$P$13)-8, ), ROWS($A$1:$A1))) Enter that formula into the first cell and confirm it by pressing CTRL-SHIFT-ENTER, and the B should appear...

Excel: Excel Folder Search

Hi Saurabh, Here is a macro you can use to do what you describe: ______________________________ Sub FindPartFiles() Checks part numbers in column B of the active worksheet. If a part number exists as a file name in the Test folder for...

Excel: excel help

It s always difficult to debug from code alone - my GUESS is that more than or less than one cell is active - so I d try putting msgbox activecell.address before the instruction to follow a hyperlink - this would confirm which cell is deemed to be active,...

Excel: Excel help

Private Sub Worksheet_Change(ByVal Target As Range) If target.Address = $C$1 Then macro =============easier End Sub Sub macro() Dim myR As Range, Vendor As String With Worksheets( Sheet1 ) Set myR = .Range( A:A ).Find( _ ...

Excel: Excel Macro

Hi Varghese, The problem I m facing while writing the macro is that A1 has no value next to 1100, when I go to A2, I find a value for 1100, but by this time, the macro has already moved on to row A2. I need to keep going forward and then backward everytime...

Excel: Excel Macro

You need to check if Not myR is Nothing right after the Set myR = ... If it IS nothing then it errors when you re asking how many there are. You can also suppress errors by On Error Resume Next, and test to see if it found an error by testing If Err.Number...

Excel: Excel Macro

Hi use this code to fill empty cells Sub rellenacel() Dim fila, uf As Integer fila = 2 b = ActiveSheet.Name uf = Sheets(b).Range( A & Rows.Count).End(xlUp).Row While Sheets(b).Cells(fila, 1) Empty d = Sheets(b).Cells(fila, 1) If Sheets(b).Cells(fila,...

Excel: Excel question

Use the a href= http://599cd.com/search/?Q=transpose+axes&key=AllExperts TRANSPOSE AXES /a feature. I cover this in my a href= http://599cd.com/site/courselist/excel2010/expert/x8?key=AllExperts/ Access Expert 8 /a class. Be sure to visit my web...

Excel: Exporting excel to .html

Hi Terrence, What you are trying to do has more to do with html code. When creating a webpage, you can add plugins to play audio / video files directly instead of prompting a save. However, as far as I know, it can t be managed through excel. Can you...

Excel: Find multiple instances of a numeric value and Return various data when the value is found

I can help a lot more easily if I had an actual workbook which illustrates what you want do to. And it d also help if you created an answer page -- what you d like the results of the macro to show. Email me the sample at bobumlas@hotmail.com use subject...

Excel: Finding Last Row Containing Value

My array method would still work, just with a wider range of data. For a non array, I think we d need to use TWO helper columns - the first would check if any value in the row matched the value being searched, so =if(countif(a1:f1,5) 0,5, ) again hard...

Excel: Formatting Macro

Sub Answer() Application.ScreenUpdating = False Range( H1 ).Select ActiveSheet.Sort.SortFields.Clear ActiveSheet.Sort.SortFields.Add Key:=Range( H1 ), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortTextAsNumbers...

Excel: formula issue

Christopher. Sure. You first argument is INDEX(StatementsRange,1,1)=INDEX(StatementsRange,1,1) so that checks if the cell reference equals itself. That should always return True. Perhaps you should change that to a single INDEX(StatementsRange,1,1)...

Excel: Formula to Pick All respective Values against one selected value

Assuming: Column A - list of ABC codes Column B - the desired values, do not have to be sorted by the way F1 - drop down where you ve selected A as you choice G1 - where the first value should appear, then going down from there: This array formula...

Excel: Help VBA

NIcole, this assumes that the three sheets you copy to are either blank or the data already in the sheets start in row 1 and are sorted ascending on column B It will search down through the existing data in the appropriate sheet in column B and insert...

Excel: Identify largest number

Because the data in the cell is NOT a number it s tricky to do - I ve put together a sheet showing you one way of getting the data http://www.aidanheritage.byethost3.com/excel/AllExpertsStringManipulation.xlsx it would be possible to combine the formulas...

Excel: Inexplicable 'Subscript out of range' error for Worksheet reference

Sven, This sounds like a problem with a windows setting. It has been a long time since I played with this but: The success enjoyed when not using the extension is dependent on a windows level setting - folders setting to hide known extensions. So...

Excel: Macro to Cut and Paste from other sheet

PJ, You would need to change this formula. r2.Offset(0, 4).Formula = =if(Countifs( & r1.Address(1, 1, xlA1, True) & , & _ r2(1).Address(0, 0, xlA1, True) & , & r1.Offset(0, 2).Address(1, 1, xlA1, True) & , & _ r2(1).Offset(0, 2).Address(0,...

Excel: Macro to Cut and Paste from other sheet

PJ, I assumed that you data is as you show and that in each sheet, the word EmpID is in A1 (to establish where the data is). I copied you data nd placed it such in a workbook with your first data in Sheet1 and your second set of Data in Sheet2. I then...

Excel: macro that will match two different column from two different sheets

This macro will do the job Sub ae() Dim cursheet As Worksheet, checksheet As Worksheet, looper As Long, checker As Long Dim blankcell As Long Set cursheet = ActiveSheet Set checksheet = Worksheets( Compre List ) blankcell = WorksheetFunction.CountA(cursheet.Range(...

Excel: moving cells and code

dp, the answer is no. VBA is an addon to Excel and it not integrated into its internal ability to adjust formulas as changed are made to the worksheet. If you anticipate such a requirement, you can use named ranges in your code. So instead of ...

Excel: Moving sections including the vba code

Hi Dave, I apologize for my slow response to your question. I have been having a problem with my internet connection and didn t see your question until just now. The best way in VBA to reference blocks of cells that could change locations is to use Named...

Excel: number of unique names with nonzero values

Assuming that data, then this answer: =COUNT(1/FREQUENCY(IF($A$1:$A$17=$J2,IF(B$1:B$17 ,MATCH(B$1:B$17,B$1:B$17,0))),ROW(B$1:B$17)-ROW(A1)+1)) That is an array formula, confirm it in cell K2 by pressing CTRL-SHIFT-to activate the array. You will see...

Excel: Can you pause formulas from updating?

Shawn, You can use the same setting you would do manually Sub Abc() Application.Calculation = xlCalculationManual Current code Application.Calculation = xlCalculationAutomatic End Sub So this sets calculation to manual at the top of your...

Excel: Pulling out names from an array and reorganizing them (the hard way)

Jari, in the sheet for the activity 1 s, in A1 put this formula A1: =INDEX(Members!$A:$A,SMALL(IF(Members!$B$1:$B$200=1,ROW(Members!$B$1:$B$200)),ROW($A1)),1) Enter this with Ctrl+Shift+Enter rather than just Enter since this is an Array formula. ...

Excel: Rename new WB created based on cell value

change & & Destwb.Sheets(1).Name & FileExtStr, _ to & & Destwb.Sheets(1).Name & Destwb.Sheets(1).Range( C2 ).Value & FileExtStr, _ Perhaps the code stops (errors out?) depending on the vaue in C2...? Special characters like : or not allowed...

Excel: Run complete macro sequentially

Mahmoud Try using the wait command in sendkeys I also suggest putting doevents on each side of the sendkeys command Dim WS_Count As Integer WS_Count = ActiveWorkbook.Worksheets.Count For i = 3 To WS_Count Worksheets(i).Activate doevents...

Excel: Run Macro from ActiveSheet Only

You have coded the variable WS1 to refer to a specific sheet - change it to Set ws1 = ActiveSheet and it should then be fine. The ONLY issue with this is it could be run on any sheet including the reporting one, so you may wish to check ws1.name to...

Excel: Run macro consecutively

Hi again Mahmoud, Yes, adding the SendKeys does not help because it only calculates the currently active worksheet, not worksheet(i), and it is always preferable to use the VBA method rather than SendKeys. Your other modifications to the code should be...

Excel: Run macro consecutively

Hi Mahmoud, In VBA you can use the worksheet object s Calculate method to force a worksheet to calculate. You can put this in a loop to calculate the sheets in sequence like this: Sub SequentialCalc() Calculates worksheets 1-3 on at a time in sequence...

Excel: Surveys into Excel Functions

Calista, Lets say you have 14 cells with a numerical rating. Say cells M1:M14 for simplicity. =IF(AND(COUNTIF(M1:M3, =4 ) 0,COUNTIF(M4:M14, =4 ) =6), flourishing ,IF(AND(COUNTIF(M1:M3, =1 ) 0,COUNTIF(M4:M14, =1 ) =6), languishing , moderate )) ...

Excel: template

This doesn t sound as though Excel is the way to go in any way - I would THINK you would need an internet sales expert - when I ve done sales I ve used paypal as the payment method, which collects the data I need. There are open source shopping site programs...

Excel: Userform

Hunch: Maybe your friend has large fonts set up in windows appearance. That often does not work very well with forms. What you might try is use the initialise event of the userform to: - Turn autosize on and then off for each label control - size each...

Excel: vba

Hi Sulabh, I had a similar question from another user. My question to you is: Wouldn t it be easier to just have a roll numbers for all the students and search only by roll numbers. That way, your data is more robust and there is no risk of a wrong name...

Excel: VBA Help

Private Sub Worksheet_Change(ByVal Target As Range) Dim trgrow If Target.Column 51 Then Exit Sub Application.EnableEvents = False If LCase(Target.Value) = yes Then With Sheets( Discharged ) trgrow = Target.Row lr = .Cells(Rows.Count, 51).End(xlUp).Row...

Excel: 2 workbooks updating a third.

I lost your email address (if I ever had it) - I can send you the updated files. Please note that in the Crew Shares workbook you have Alister and in the Stelissa workbook you have Allister -- if they don t match exactly, they won t be able to be updated....

Excel: Adding a Loop to macro

Kauther, I tested this and it worked for me as long as there was data in each column of cells in rows 1 to 8. It processed the range B1:FQ8 Sub RepeatColTexttoColumn() RepeatColTexttoColumnMacro For i = 2 To 173 columns B to Cells(1,...

Excel: calculate a school year group based on the child’s date of birth

I suspect there is a simpler method than the one I m going to outline, but I don t know the ins and outs of the Australian school system (I m sure that you could use a given date in the year to mathematically calculate the data you want). My suggestion uses...

Excel: Conditional formatting

Try this tip video on a href= https://599cd.com/tips/excel/conditional-formatting/?key=AllExperts CONDITIONAL FORMATTING /a . Be sure to visit my web site and watch my B FREE /B 90-minute A HREF= http://599cd.com/Excel/AllExperts Microsoft Excel...

Excel: Conditional Formatting

On a new Excel workbook, enter into cells A1:A3 15:00 15:57 16:01 Select A1:A3. Choose from menu: Home, Conditional Formatting, New Rule, Select a Rule Type: Use a formula to determine which cells to format In the text box labeled Format values...

Excel: Conditional Formatting in Excel 2010

Bas, Select column A with the first cell as the active Cell. So say A2:A500 is selected and A2 is the active cell in the selection Then on the home tab select conditional formatting then select new rule then select: Use a formula to...

Excel: converting # of days to actual date

Kathryn, Excel Dates are stored as the number of days from a base date. For windows, that is Midnight on 31 Dec 1899 as becomes 1 January 1900. That really isn t of any significance. What is of significance is that to add 150 days to a date, you do just...

Excel: Copy text data across sheets to/from non-adjacent columns

Ryhs, You can send a workbook to me and I will take a look. I assume Excel 2011 is a Macintosh version of Excel. I only have windows. I can try writing a macro that works in the windows version (Excel 2010 is the highest version I have installed) and...

Excel: With Events

Craig, John Walkenbach has a good example of doing this creating a single click event for multiple buttons on a userform. The method puts all the objects in an array. See the approach at his page: http://spreadsheetpage.com/index.php/site/tip/handle_multiple_userform_buttons_with_one_subroutine/...

Excel: Excel-Drop Box

Hi Amelia, 1. Create that list somewhere in your sheet. Lets say you did that on Sheet2!A2:A10. Make sure cell A1 has a header for the list, make cell A1 Bold. 2. Convert the list to a Table (Home tab, Format as table). That way you can add items to the...

Excel: Excel formula question

%20.00 is not a standard percentage format. Forgetting what that LOOKS like, is this an actual number in that cell formatted to appear that way, or is it actually %20.00? When you click on the cell and press F1 to open the CELL FORMAT wizard, what is selected?...

Excel: Fill column

Hello Chris, Thank you for your question. Assuming you are starting with cell A1, type 1 -0 in cell A1. Then place this formula in A2: =(LEFT(A1,FIND( ,A1)-1)+1)& -0 Copy that formula down to row 45. Each row will increment the number...

Excel: Given Birth Dates : Require Age

I d use the YEARFRAC function - in older versions of Excel this wasn t native, so you d have to use tools, add-ins to add in the Analysis ToolPak - later versions have it as standard. The format is (specifically for your case) =YEARFRAC(A1,NOW()) where...

Excel: How not to graph blank cells

Michael, Right click in the plot area and pick Select Data. the data dialog appears and in the lower left corner it shows hidden and blank cells. Click on that button. It probably has zero selected (radio button). choose Gap. If you are the...

Excel: Import Selected cells values

Need LOTS more info re step3: ..add selected cells values to column C, D,E.... What do you mean by selected cells ? Nothing is selected if the workbook wasn t even opened! What s special about cell C55? What does ..paste me cell #C55#... mean? How...

Excel: macro to return pictures

I ve seen this type of question a few times, and have found various possible answers - the current best two (I think) are http://www.excelguru.ca/content.php?279-VLOOKUP-for-Pictures http://www.excelforum.com/excel-programming-vba-macros/834481-multiple-picture-lookup-with-mcgimpseys-method.html...

Excel: macro in vlookup

Saurabh Sorry but I do not know how to get the picture to transfer over. The picture is an object within a cell or a group of cells and It doesn t seem to want to come over to the new location since its unrelated to the cell itself. I suggest you reask...

Excel: Macros

Ashish, VBA is pretty close to basis Sub Countto10() Dim i As Long, j As Long For i = 1 To 10 j = Int(Rnd * 100 + 1) if j 70 then MsgBox Count is & i & random number is & j End if Next i End Sub So this demonstrates using...

Excel: Pre-loaded pictures macro solution?

Hi Jim, Here s code to do what you describe. Read the code comments for important assumptions. __________________________________ Sub WXpics2Table() copies pictures of Sun, Cloud, Rain, and Snow from Type Pics sheet into table cells containing...

Excel: SUMIFS

Svein: You cannot use an array, K23:K24, as a search value within a SUMIF (or SUMIFS) function. You will instead need to use a function designed to accept array input - I suggest SUMPRODUCT. Here is the function you would use in Microsoft Excel: ...

Excel: Vlook up macro to return pictures

Run this with Sheet2 active: Sub PutInPix() On Error Resume Next Dim n As Integer For i = 1 To Range( A60000 ).End(xlUp).Row n = 0 n = Application.Match(Cells(i, 1).Value, Sheets( Sheet1 ).Range( A:A ), 0) If n 0 Then...

Excel: vlookup macro to show pictures

Saurabh: Excel does not natively support this; however, I am familiar with trick that mimics what you want. Please refer to the following website for a detail description of picture lookups: http://www.excelguru.ca/content.php?279-VLOOKUP-for-Pictures...

Excel: vloopkup VBA code

Hello Saurabh, Here is the solution I promised yesterday. The way it gets around the VLOOKUP is by naming each picture a name with the part number embedded in it, in this case simply a string that is the serial number with P_ prefixed. Because it directly...

Excel: What's wrong with my excel workbook? :(

Hi Ryan, Basically what you are asking for is as follows: You would enter certain data with today s date. This data should get stored in the next sheet. On the next day, you will enter fresh data which should be stored in a new line on the next sheet and...

Excel: What's wrong with my workbook?

Ryan I can t read your worksheets, so I can t see the formulas you are using, however I believe that you are simply overwriting data by using the same formula that transfers to the same location. You may be able to resolve this by transfering the data...

Excel: Adding hours to Start date to get workday

What about B1? Since there are only 3 parameters in the WORKDAYS function, =WORKDAY(start_date, days, [holidays]) ... you just need to decide where you want it to slip in. Pure guess on my part would be on the days calculation. Currently...

Excel: Calculating standardised score through Age and a Raw score

Maz, If you knew the formula that was used to create the table, then you could use that formula. I don t know if that is revealed anywhere. If all you have is the table, then you could enter that table in individual cells in a a worksheet and do a two...

Excel: Check Boxes to Hide Columns

Hi Keith, Sorry for the belated reply!! Somehow, some of the cells contain more than one hyperlink (I didn t even know you could do that!). The second hyperlink appears to point to the right location. Solution would be to remove all hyperlinks and...

Excel: Check Boxes to Hide Columns

Hi Keith, Add this code to a normal module and assign each checkbox to it: Sub CheckBox_Click() Dim sName As String Dim oCell As Range Dim bCheck As Boolean Dim lCT As Long For lCT = 1 To Worksheets( Details ).CheckBoxes.Count...

Excel: Color the Range of Cell when SUM reaches upto 100

How about this? Sub ColorCellsThatTotal100() Dim oCell As Range Dim dTot As Double Range( A1:A100 ).Interior.Color = xlNone For Each oCell In Range( A1:A100 ) dTot = dTot + oCell.Value If dTot = 100 Then ...

Excel: Color the Range when Sum reaches upto 100

Sub colorCells() before running this procedure, select the range it should apply to. the range should be 1 column wide Dim topCell As Range, over100cell As Range Dim multiCells As Range, inThisRange As Range Set inThisRange = Selection ...

Excel: Conditional Formatting

(1) As I said, there s no such time as 24:00. Midnight is 0:00, not 24:00. And even if there were a time of 24:00, there SURELY is no time LATER than 24:00, so a2 TIMEVALUE( 24:00 ) makes no sense, since after 23:59:59, the next second is 0:00:00. (2) Why...

Excel: Convert excel Max function to VBA formula

mine, send a sample file to twogilvy@msn.com (You don t need to have service records for 800 vehicles in it - only enough so I can test the code and understand what you are doing - but make sure the named ranges are in the file). since you...

Excel: countif and counta with text

hello, use this macro: Sub sum() Dim fila, fila1, contat, conta As Integer fila = 1 fila1 = 8 While Cells(fila1, 1) Empty While Cells(fila, 1) Empty If Cells(fila, 1) = Cells(fila1, 1) And Cells(fila, 2) Empty Then conta = 1 contat = contat...

Excel: Create outlook email or task if a value changes on a spreadsheet cell

Hi Costa, You re right, we need to create a macro. I can do that part for you. It should be a simple macro. I just need some details: The changes that should trigger a mail Time when the mail should be triggered - on file open, save or close? The id...

Excel: Delete duplicate entries

Donald, This worked for me with the data you show: Sub comb() Dim i As Long i = 1 Do While Cells(i + 1, 9) If Cells(i, 9) = Cells(i + 1, 9) Then If Cells(i, 10) = Cells(i + 1, 10) Then Rows(i + 1).EntireRow.Delete ElseIf...

Excel: Difficulty with time functions

On a new Excel sheet, select column C. Right-click on the selected range, and from the menu choose Format Cells. From the Format Cells dialog box, choose Number, Category: Custom, Type: mm:ss.00, OK. In C2, enter: 00:45 In C3, enter: =C2+1/24/60/60*0.04...

Excel: display the number beside a 0

As your question suggests that the data will be a sequential block of data, this formula =MATCH(0,B1:Z1) would return the first cell with a zero value. That can then be used with other functions to get your value - I d suggest offset - =OFFSET(A1,0,MATCH(0,B1:Z1)-2)...

Excel: Excel Automatic Update From One workbook to another workbook in excel

I have set it to run from the Active Workbook, which means it needs to be the summary file that is active when you run it. I will take a look at the rough file when I get home (cannot access file share sites from current location) and update this reply at...

Excel: Excel Formula

In cell A10, enter: 600 In A11: =MAX(IF($AA$11:$AA$500 0,IF(ROW($AA$11:$AA$500) A10,ROW($AA$11:$AA$500),0))) as an array. I.e., after typing the formula, instead of hitting Enter key, hit Ctrl-Shift-Enter. In B11: =INDIRECT( AA & A11) ...

Excel: Excel Question

Assume the scores are in AA11:TF11. First, enter into AA12:13 the following formulas: =IF(AND(AA11 ,COUNTA($AA11:AA11) 21),AA11,9999) =IF(AND(RANK(AA12,$AA$12:$TF$12,1) 11,MAX($Z13:Z13) 10),MAX($Z13:Z13)+1,0) Then copy AA12:AA13 to the right, through...

Excel: Finding the last >0 entry in a row

Pete, Try this formula in AZ3 =LOOKUP(10000,C3:AY3) just make the 10000 any number bigger than a value that will appear in C3:AY3. If the numbers will be less than 10000, then just leave it as 10000. This should return the value of the last cell...

Excel: Form, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi, Thanks for sending your question on my way. Populating the field data from PDF to excel is little bit hectic one. I suggest you to build the same in excel, so that we can easily retrieve the required data with a simple loop in excel VBA. Another...

Excel: Formatting changes from main spreadsheet to other spreadsheets

Hi Anthony, There is no way to link formats through formulae You have two methods to handle this- 1. Use the format painter, select the source formatting in sheet one and apply it to destination sheet. 2. Copy the source cells and use paste special to...

Excel: Formula for blinking or flashing range of cells

Right-click the sheet tab of where you want this to happen, select View Code, enter this: Private Sub Worksheet_Change(ByVal Target As Range) Application.EnableEvents = False For i = 1 To 10 Range( J10:J500 ).Value = False Application.Wait...

Excel: Formula help

To get 7 days (or the preceding weekday) use this - for simplicity, I have my date in A1 =IF(WEEKDAY(A1)=1,A1+5,IF(WEEKDAY(A1)=7,A1+6,A1+7)) For 14,21 and 28 days I would then simply add a further 7, 14 and 21 days to the value calculated as once you...

Excel: Help in Combinations

If you want any 10 consecutive numbers you can simply amend the for..next loop. If as is more likely the numbers are not consecutive then this modified version would work for you. Sub aidancode() Dim a, b, c, d, e, f As Long Dim outvar As Long Dim...

Excel: Highlight a Range

hello Use this macro incorporates a module copy and paste the code Sub Sel() Dim fila, conta, contat, filar1, filar2 As Integer fila = 2 conta = 0 contat = 0 filar1 = 2 filar2 = 2 While Sheets( hoja1 ).Cells(fila, 2) Empty conta = Sheets( hoja1...

Excel: Index/Match/Offset?

Gail, I would do it this way =SUMIF($A$5:$BB$5, June ,$A$2:$BB$2)+SUMIF($A$5:$BB$5, June ,$B$2:$BC$2) this was tested and worked for me. Row 5 must hold the text string June and not be a date occurring in the month of June. You can change...

Excel: Match cell content then return back the value of cell next to it

Donald, Lightly tested, but this worked for me. Sub EFG() Dim i As Long i = 1 Do While Cells(i + 1, 1) If Cells(i, 1) Cells(i + 1, 1) - 1 Then Rows(i + 1).EntireRow.Insert Cells(i + 1, 1) = Cells(i, 1) + 1 Cells(i + 1, 2) = 0 ...

Excel: Modifying multiple absolute references

Pete, Well, that isn t what you asked in your first question. To alter the formula within a cell, then you would need a macro to do that. I selected X535 which had the formula =A355 and ran the below macro and it changed the formula to =IF(A355=...

Excel: Need help in excel

Hi Rajeev, Can you tell me how you got the date picker into the sheet. Possibly share the file if you can. I m guessing there should be an easier way for you to pick dates depending on the kind of requirements you have. You can either share a link to...

Excel: Pivot Table

Francis Ang, Dim pt as PivotTable Dim pf as PivotField Dim pvtitm as PivotItem set pt = Activesheet.PivotTables( PivotTable4 ) set pf = pt.PivotFields( PRODCD ) pt.PivotCache.Refresh pf.ClearAllFilters for each pvtitm in pf.PivotItems if pvtitm.Value...

Excel: Pivot Tables/Dates

Hi Karen, Can you send me the file you are trying to use with some sample data filled it. I m sure it s pretty easy to sord data by date order. Please send your file to gulshanrajpurswani@gmail.com and mention allexperts in the subject line. Thanks,...

Excel: Pulling Last Cell Value From Other Worksheets

Rachel Construction of formulas very much relates to how the data, from which you are pulling, is situated in your monthly sheets. Because of that, it would be very helpful if you could send me your workbook. I will take a look and see if we can come...

Excel: Query related to Pivot table

Hi Abhimanyu, I m not sure how you would get a pivot table output. However, you can get the similar result by using countif function in excel. Eg, in table 2, for number 1 and A combination, you can use this formula =countif( your range containing a,b,c...

Excel: Repeating IF Statement

You can use just a blank spaceholder or some message for the unsure from here part, this will populate only those cells which have the dates in your range, others will show a message. You ll ofcourse need to have to copy the formula to the whole column...

Excel: Replace cell with different text

Hi Michael How are you? Either you play around with the argument What:=MID(cell, start character, number of charaters) ... the exemple I attached Mid(cell, 1,2) looks for the first 2 characters of cell ( Re of Resistor ) and makes the replacement. ...

Excel: Subtracting house from date

Michael, do you mean so that the output appears in another cell? if so then assume the cell with the first date/time is A1 and in B1 you would put the formula =A1-TIME(4,0,0) that worked for me. (make sure the cell with the formula is formatted...

Excel: sum values down a column until they meet or exceed value x

Sarah, sure, emailing your sheet would probably be much better. So basically, summing column H determines where the sum of values from column I will appear. In you example, you sum row 3 in two places which I believe is an error in your explanation....

Excel: Time Value and Conditional Formatting

Select cell C10. Apply the time format you want to cell C10. Then from Excel menu: Home, Conditional Formatting, New Rule, Use a formula to determine which cells to format, Format values where this formula is true: =AND(ISNUMBER(A10),A10 =0,A10 1), Format,...

Excel: Troubleshooting filter use

First, I don t use Mac Excel - UI is pretty different. However, the up/down arrows next to attribution looks like Data Validation list, not Filter. Also, the blank cell above existence may be the issue in that your real filtering of Other ONLY is being...

Excel: USING EXCEL TEMPLATES

I want to say yes , but the phrases you re using are confusing. 1) Can you import ASCII files into Excel? Yes, you can import any text file into Excel. 2) Can you save a spreadsheet that has been created after importing an ASCII file into it? ...

Excel: VB Copy Range with Lastrow to new workbook

david, I have no way to determine what the name of your destination workbook is so I will give you an example with a known name. Sub ABC() Dim bk as workbook, sh as worksheet set bk = workbooks( MyBooks.xlsx ) set sh = bk.worksheets( Sheet1 ) Activesheet.Range(...

Excel: Waterfalls/Pivots

If by waterfalls you mean waterfall charts, there is no such builtin format, so you already need to do some manipulation to make a waterfall chart and I don t know the way you have your data laid out. I think I can help a lot better if you can send a sample...

Excel: worksheet, Excel Sixthsense Excel

Excel Sixthsense Excel: Hi, Thanks for giving a brief about your expectation which given a clear insight of what you are looking for. Split screen can t be used on another sheet and it can be used only with the current sheet alone. At the same time there is no inbuilt excel...

Excel: adding time in an excel spreadsheet

A minute is 1/1440 of a day in Excel time calculations. If you had a time in A3 of 8:00am and a value of 5 in B1 to represent 5 minutes, you could see the new time with this formula in B3: =A3+(B1/1440) See the attached picture, that formula is...

Excel: Automatic updation of the corresponding sheet

People do this all the time, try to create daughter sheets to reflect a subset of data from the main parent sheet. This is usually unnecessary plumbing, Excel has fast filtering capabilities that allow you to see the data on the main sheet filtered in exactly...

Excel: Calculating number of times a value appears

Michael, I am having no problems receiving email - as an example, I received notification of this question . Was the attachment a multi-megabyte file. Perhaps just sending a representative file would be sufficient. twogilvy@msn.com -- Regards,...

Excel: Check the data from two sheet and result on third sheet

Hi Shivendra, I am a bit confused. You mention checking in the PF sheet for the Emp. no, and if it finds it then check relationship and first name, last name. So I assume these data are all located in the found Emp. no. row of the PF sheet, but you do...

Excel: EXCEL 2010 / 2003

Mike, I would see this as a conditional formatting requirement. You would need to set up conditional formatting to text whether the value in one sheet differs from a value in the other sheet based on match cube number. you can use =Vlookup(A2,UserData.xls!Sheet1!$A:$D,2,False)...

Excel: Excel

Same steps as before, but change the formula in C2 to: =IF(SUMPRODUCT(--(LEFT($A$2:$A$100,LEN(A2)-1)=LEFT(A2,LEN(A2)-1)))=10,LEFT( A2,LEN(A2)-1)*10, A2) Obviously you will need to expand A2:A100 reference to encompass the data in column A, do not...

Excel: Excel

Sabah, Based on your description, it looks like your 27/12/1978 is being stored as a string rather than as an excel date. This formula should handle either situation but if 27/12/1978 is stored as a string, the formula assumes the string in written in...

Excel: excel

Hi Ravisankar, You are basically expecting excel to automatically divide all your values by 1000. This is possible a straightforward formula - Enter your value in cell A1. Enter the following formula in cell B1 =A1/1000 . However, if you want to do...

Excel: Excel Macro Copy and Paste Question

Hi Jim, I can t read the worksheet sample you posted. It is just too small, and in addition doesn t contain the TO sheet example. Could you email me these screen shots? My email is VBAexpert@myway.com. From your textual description of the problem...

Excel: Excel Solver

Thanks for the question Claude - all is well here! To use Solver, I think you are going to need to use A5 as well - into which enter =a4/a1 Solver should THEN work by letting you change A1 to get A5 to be a value of .1 The answer I got for this using...

Excel: Extract a List of Values Filtered by Criteria with Sub-Arrays

Timothy, basically you can do =if(row() =countif( Car Data (Namibia) !$H$2:$H$1156, = & Filtered List !$A$2),Namibia Formula, Zambia formula) You would have to adjust the row() to return a number that can be compared to the countif. Since I don...

Excel: If formula with 2 conditions

Hi Lauren! How are you? actually you do not need to use SumIF for summng the 1s of column S as this has only 1 or blank but if you want to see how to use sumif, please try the following =Sumif(C2:C14, x ,S2:S14) or =Sumifs(S2:S14,C2:C14, x ,R2:R14,...

Excel: Formula reworking

Chris Mitchell, First, you show E10 =IFERROR(INDEX(TERMDATA,MATCH($C10,termdatarows,0),MATCH(E$9,termdatacolumns,0)), ) but your description says that E10 contains AUT 1 and this formula should be in E11. Looking at the formula might indicate...

Excel: formula rewrite

Chris Mitchell, I tested the formula you show with the conditions you described and it worked fine for me. When the vlookup returned a matching value it returned correct. When I changed it to a non-matching value (in sheet1), it returned incorrect....

Excel: fromula rewrite

Chris Mitchell, =if(and(vlookup,a3,Sheet1!$B$1:$C$159,2,false)=d1,c1=n2), correct , incorrect ) should be =if(and(vlookup(a3,Sheet1!$B$1:$C$159,2,false)=d1,c1=n2), correct , incorrect ) but since you seem to imply that your formula is working...

Excel: locking cells

Nancy Yes you can do what you want to do. It takes a series of steps, such as locking the cells, giving specif access to certain cells, etc. I don t normally do this but the help section of excel explains pretty well the steps you need to take and the...

Excel: multiple if statements with Vlookup

Leslie, Sorry for the late response. My internet connection has been down for over 24 hours due to a cut line. I tested your formula and it worked fine for me. (Excel 2010, Windows 7) I tested with all three names in the same locations you show...

Excel: Outcome in TRUE or FALSE format

Rakesh, say that value is (TU,FR) is in cell A1 then say MON is in G30 in G31 put in =IF(COUNTIF($A$1, * &LEFT(G30,2)& * ) 0,TRUE,FALSE) When I dragged G31 across I got: MON TUE WED THUR FRI SAT SUN FALSE TRUE FALSE...

Excel: overtime formula

Sorry it took so long to get back to you - I was away on vacation. I don t understand ...but I want the reg time to get to forty. What do yuo mean by that? Right now the total of the regular time is 25.5. Are you saying you want this to keep showing the...

Excel: recover corrupted file

A file name that begins with ~ is usually an indication that it s open somewhere. In any case, corrupted files are incredibly difficult to remove the corruption because it s nearly impossible to know where that corruption is. Best bet is to try to open it...

Excel: Showing Duplicate rows even city are fliped

Rakesh, You can use and array formula =if(($C$2:$C$100=$B2)*($B$2:$B$100=$C2)*($D$2:$D$100=D2),Row($D$2:$D$100)) If you enter that in a cell in row 2. Then select that formula in the formula bar and hit F9, it should return an array of 100 values...

Excel: 2 Tables on one chart

Ahmed Ezzat, I have posted an example image. I selected the first table and created a line graph. I then went to the second table and copied the names and the data. I then when to the chart and selected paste. That put the data from both tables on...

Excel: "[@[Acquisition Cost]]<>0", Excel Sixthsense Exce

Excel Sixthsense Exce: Whenever you see brackets in the reference then it denotes that it refers to Table References. In your example [Acquisition Cost] refers to one of the column header of that table. That column header name is Acquisition Cost. @ refers to current row...

Excel: Array

Bill, In you second condition you have M&A !$B$3 If you follow pattern, that should be just $B$3. this worked for me: =IFERROR(MAX(IF(( M&A Database !$S:$S=O$3)*( M&A Database !$N:$N=$B$3)*( M&A Database !$I:$I 1000), M&A Database !$P:$P)), NMF...

Excel: Auto sort formula

Real answer to your question: no. However, you CAN do it by a macro and it ll happen automatically (because of the macro). Say your data starts in A1. Right-click the sheet tab, select View Code, enter this (copy/paste) Private Sub Worksheet_Change(ByVal...

Excel: capture live data to excel in tablular format

Hi Siraj, I tried searching through the page content. The problem is that I m unable to identify the individual table objects on the page. This is necessary to point the excel to the right table and save the data correctly every time. The core requirement...

Excel: capture live data to excel

Hello again Siraj, While I didn t succeed in fixing the problem as it affects connecting to investing.com, I did get it to work with other websites. Apparently investing.com thinks I am using IE 7, a way outdated version of IE, when in actuality I m using...

Excel: Chart X axis Label

Parantapkumar Sorry for the late answer-- I did not see your question-- Unfortunatly I am not an expert in Charting since I seldom use them. I suggest you reask the question of another allexperts person and I m sure you can get a good answer. Richard...

Excel: Conditional formatting for a row

Bill Select rows 3 through 69 with cell A3 as the active cell Select conditional formatting on the Home tab, then select New Rule select Use a formula to determine which cells to format in the formula text box put in =$H3 Click the...

Excel: copy and paste to new workbook

Chris Mitchell, If you mean manually, you would need to create the new workbook/worksheet. Select the range B1:P1500 in the source worksheet go to the destination in the new worksheet, do a normal paste. Then do a paste special and select values...

Excel: Counting Cells with dates

Hi Tony, Sorry for the delayed response. You can run a pivot table report on this data and set the rows by month and get the cound by month. Alternatively, you can also use the subtotal formula on the range and then set the subtotals to show count instead...

Excel: Counting Numbers in an excel spread sheet

An interesting question - and one which I was convinced a previous question some time back would cover - took me a while to find my example sheet, but I think that http://www.aidanheritage.byethost3.com/excel/Lottery_Pattern_Check.xls will give you...

Excel: dynamic chart assistance

Sandy, Just build a chart from you existing data table using only columns B and C So you chart will look like one of your examples, but will have all six items in column A. Now select your table and apply a filter. Filter on column A to select one...

Excel: Excel 2007 Add-In

Cheung, It sounds like you want to use addins, so there are a lot of things to know. I would expect the s subroutine to be visible in the list of macros. If it was, it would show as Addin_Name!s You need to make sure your procedures are in a...

Excel: Excel 2010

Chand I do not know-- If you use a vlookup formula the formula will transfer over the value in the cell you choose to transfer from to the cell you choose to transfer to, but to my knowledge the value does not include what pictures you may have in the...

Excel: excel

sabah, If you are using Excel 2007 or later, you may not be familiar with the new Countifs function. (Note the s on the end of the countif) This new function introduced in Excel 2007 operates like the original Countif function but allow you to specify...

Excel: excel

Sabah, What you describe would require a macro. However, since you didn t try to construct a macro, then I assume you would want to use a formula. Say you data starts in row 2 of each sheet with headers in row 1. Then in sheet2 (not sheet1), in B2 you...

Excel: Excel

Sabah, you can put his in a general module in your workbook (insert= Module in the VBE) then you can put this in a cell =GetScore(A1) for example. Public Function GetScore(r As Range) As Variant Dim i As Long, j As Long Dim s As String, s1...

Excel: Excel

Jackie: If track changes is disabled then Excel retains no accessible history. The key is to prevent someone from being able to disable the track changes option. I suggest the following - Protect and Share workbook instead of just Share workbook...

Excel: Excel 97-2003 and Excel 2007

Hi Art, With a file size of 43 MB I am not surprised about the slow speeds you are experiencing. Your system certainly has enough resources to be able to handle very large workbooks, but I suspect that Windows is not putting the entire process in RAM and...

Excel: excel formulas/if functions

I think the remaining time formula is actually f2-today() as otherwise you d end up with a negative number, but that aside =IF(C2 15, Send , No ) should do what you want. I MIGHT be inclined to do =IF(C2 15, Send , ) instead which would ONLY...

Excel: Excel If Steatment and Drop down list

It s difficult to follow this via the medium of allexperts, which is text only. It sounds like it s possibly some form of array filter - if it is possible to see an example spreadsheet, this would help in providing you with an answer - both from my perspective...

Excel: Execl

I would do it like so: - Have ONE sheet with books. Add a column in which you can mark books as Dropped. - Format that range of cells as a table (Home, Format as table) - In each account sheet, have a table in which the first column holds the names (or...

Excel: Filtering a column with a list of values seperated by dashes

Hi Mo, You can use custom filters to achieve this result. First activate filters on this list. Now, click on the filter arrow on the headings row and select custom. Add one condition as contains / Select and option Add second condition as ...

Excel: formula issue

Chris Mitchell, As written, U18:U1500 covers 1483 cells BY28:BY1509 covers 1482 cells. they need to cover the same number of cells so perhaps =COUNTIFS(U18:U1500, 0 ,BY28:BY1509, 13 ) should be =COUNTIFS(U18:U1500, 0 ,BY28:BY1510, 13...

Excel: formula issue

Chris Mitchell, You are not completing your countif formula with a right parenthesis before you start your next IF statement. I expect this is what you want =IF(Main!C10=7,COUNTIF(Sheet2rng!$B$2:$B$1500,7),IF(Main!C10=8,COUNTIF(Sheet2rng!$B$2:$B$1500,8),IF(Main!C10=9,COUNTIF(Sheet2rng!$B$2:$B$1500,9),IF(Main!C10=10,COUNTIF(Sheet2rng!$B$2:$B$1500,10),IF(Main!C10=11,COUNTIF(Sheet2rng!$B$2:$B$1500,11),...

Excel: formula rewrite

Chris Mitchell, One way would be: =IF($H$2= SUM 2 ,AND($B18 ,INDEX($J18:$O18,1,$A$4) IF($A$4 =3,-2,-1)),AND($B18 ,INDEX($P18:$U18,1,$A$4) IF($A$4 =3,-2,-1))) But it looks like you only want to change the Index range based on the text SUM 2...

Excel: fromula rewrite

Chris Mitchell, I think you just have a typo in your formula. If you want to test if B18 is blank, then show blank then it would be =If($b18= , ,IF($H$2= sum 2 ,CONCATENATE($B18, , ,$C18, , ,I18), )) an alternate approach would be ...

Excel: Help with formula

The omits adjacent cells is a warning message that there may be a problem with the formula, but it isn t an error as such. It simply suggests that Excel thinks you may have made a mistake. As to the autofill, excel will apply logic to fill. If you fill...

Excel: Icon-Set Arrows

Oai, I have attached an image that shows the settings to make in conditional formatting, icon sets when you choose more rules (last choice underneath list of icon sets). Before doing this, select the data. If you want the arrows in a cell next...

Excel: Sum Product formula

Donna, Assume column C is the ID2 column then If you want to match if there is a match in column C or (if column C in the Static sheet is blank and column C in the worksheet is not blank) then =IF(SUMPRODUCT((Sheet2!$A$2:$A$50=$A2)*1,(Sheet2!$B$2:$B$50=$B2)*1,(Sheet2!$C$2:$C$50=$C2)+((Sheet2!$C$2:$C$50=...

Excel: Live data capture from website.

capturing data is relatively simple. I m assuming a recent version of Excel. Use the Data Ribbon, and then the from web button on the Get External Data section. Specify the website and where to store the data and Excel will build a web query to get the...

Excel: OpenOffice

This is quite simple Nora. First you should protect the sheet and then go ahead and hide it. This way, even if someone unhides a sheet, it will still be protected and the user may not be able to view the formula. This is how it works in MSExcel. I don...

Excel: page numbering

aamir, OK. I understand on the email - just wanted to let you know that I can not see your email address - it is hidden from me. Nonetheless, it appears you have received my answer. Hope it answers all your questions. -- Regards, Tom Ogilvy ...

Excel: PAGE NUMBERING

aamir, Susan Barnhill give assistance on word. I give assistance on Excel. However, assume you are speaking about Microsoft Word then If you go to the Page Layout tab and then on the Sheet Options button on the lower part look at the little box with...

Excel: paging

aamir As I stated, I do not have office 2013. I did provide articles that are specific to Word 2013 so I suggest you refer to them. They should give you the information you need. Sorry - but as I stated originally, my expertise is in excel. At that...

Excel: Put Password on each workbook

ACHILLES, You can make a function Public Function Generate() Dim strDP As String, lRndx As Long strDP = For lRndx = 1 To Int((18 - 12 + 1) * Rnd + 18) If Int((2 - 1 + 1) * Rnd + 1) = 1 Then symbol or number strDP...

Excel: Pivot Table

Hi Frank, I m not sure I understand your question. In your source data, you don t have a Mr. H but in expected result, you are showing a Mr. H. Further the logic on which you are querying the data is not clear. Can you explain you requirements more...

Excel: Pivot table auto populate

Ricardo: Yes, there is. When setting up your pivot table, set the data range for the table to be much larger than your original data. For example, if your data is in cells A1:B132... set the range of the Pivot Table to A1:B1000. Then, whenever you...

Excel: Reverse selection

Updated for consistency -- Follow all steps exactly - assumes the top L is in cell D4. Enter 2 in E4 and E5 (don t worry - this will become S1 or S2) In E6: =IF(COUNTIF(D3:D5, L ) 2,E5,IF(COUNTIF(D4:D5, L )=2,3-E5,E5)) Fill E6 down to E15 (or more...

Excel: Selecting specific pivot items with VBA

Francis Ang, You actually don t want to de-select ALL A pivotfield should always have at least on visible item or you will get an error. You would do the opposite. Make all items visible, then hide the items you don t want. Here is some tested...

Excel: sum of same font values

Chris, In a single module you could have 4 separate functions which have different names. For example: Function SumByGreenFont(InRange As Range) Dim Rng As Range Application.Volatile True For Each Rng In InRange.Cells If Rng.Font.ColorIndex...

Excel: Sum of rows

Assuming the 100 in your figure is in cell C2: if you have =SUM(C2:C3) in cell D3, for example, select D2:D3 (D2 being blank), click on the fill handle at the bottom of D3, and fill down as far as you need. You ll see the formula you want in D3, D5, D7,...

Excel: Summation of values in columns

You can use the MOD function to identify the columns for every 7th value. =MOD(A1:AU1, 7) = 0 That will collect all the values except for A1 which is NOT in one the 7th columns, that column must be added manually. The result in AW1 would be: =A1...

Excel: Table, Pivot Table formula help

Donna, The details in your image are extremely small - they are unreadable. Since you are depending on them to provide the details behind your question, they don t and I really can t advise you. If you want to send the workbook you have captured in your...

Excel: Today() in a sumifs formula

Missy, Not being an accountant, I don t have any particular knowledge of an aging report. However, given the formula you show, =sumifs(b4:b1000,a4:a1000, =TODAY() ,a4:a1000, TODAY()-30 ) You would need to do =sumifs(b4:b1000,a4:a1000, ...

Excel: VBA excel limit access to .xlam based on criteria

This depends on a lot of things. I don t know of specific documentation online for this, but here s things to consider: • on startup (workbook_open event) I assume you re prompted for the client name (if not, this would be a good idea) • set the client...

Excel: VBA: opening/comparing two workbooks

Steve, Here is some sample code that worked for me as I understand your description: Test it on copies of your workbooks until you are satisfied it does what you want. Sub abc() Dim bkpre As Workbook Dim bkpost As Workbook Dim shPre As Worksheet,...

Excel: IF AND IF

It s a combination of AND and OR which would work something like this =IF(AND(OR(AM2= 9th ,AM2= 10th ,AM2= 11th ,AM2= 12th ),AP2= x ), x , ) Your explanation of the question and the example formula didn t quite agree, hence my using 9th 10th etc...

Excel: 2 way sync between Excel 2013 and Acces 2013

Here are some pages that may help: http://support.microsoft.com/kb/278973 http://www.exceltip.com/import-and-export-in-vba/export-data-from-excel-to-access-ado-using-vba-in-microsoft-excel.html http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=171:microsoft-access-activex-data-objects-library-ado&catid=79&Itemid=475...

Excel: Adding if(iserror to a range of cells

Gail, If there is a unique combination of characters at the beginning of your formula you can use the Replace command from the right side of the home tab of the ribbon. But generally that is not the case. (you might have to do multiple find and replace...

Excel: Averageifs

Christopher Mitchell, You pretty much answered your own question in your subject: AVERAGEIF(range,criteria,average_range) Range is one or more cells to average, including numbers or names, arrays, or references that contain numbers. Criteria...

Excel: conditional color to another cell

Pete emrich, VBA can not see a color produced by conditional formatting. The workaround is to use the condition used by conditional formatting (I assume you know what the condition is) and determine from the value in the cell and the conditional formula...

Excel: Conditional Formatting

Chris, If you have some cells that have a red font color manually set and you have some cells that have a red font color based on conditional formatting with a rule of =A1 5 in the range from A1 to A100 then you could loop through them note: the rule...

Excel: Conditional formula question

Hello Cindy, Thank you for your question. I believe I can help you with this. However, I am not for sure if you are wanting something (like Male or Female ) placed in an adjacent cell, or, if you are wanting the ID number formatted differently for...

Excel: converting formulas to vba

Christopher Mitchell, at its simplest it would be With Worksheets( Main ) .Range( B2:D100 ).Formula = =iferror (vlookup (b2, temprange, 2, false), ) .Formula = .Value End With that would place the formula and it would be evaluated. The...

Excel: Copy One Chart Series' Format to another Series

ActiveSheet.ChartObjects( 1 Gráfico ).Activate ActiveChart.Legend.Select ActiveChart.Legend.LegendEntries(1).Select Selection.Format.Line.Style = msoLineSingle Selection.Format.Line.Style = msoLineThickThin With Selection.Format.Line...

Excel: copy data using vba

Nabam, that is what the code should do. In your original question you said: then copy the corresponding row data of column A and column D of sheet4 to sheet2 corresponding row in column A and E the code does that with sh2.Cells(cell2.Row,...

Excel: Count Clicks of Spin Buttons

I can try to do it step by step, though I did think I more or less had! Developer ribbon, then use the design mode button to get the controls into design. Right click the spin button control and you will have a view code option - left click that - it will...

Excel: damon-ostrander's Vis code

What you have described is the correct method of applying it, and the function should work as written - POSSIBLY you have a lot of data which is causing the function to hang? In my test sheet with minimal data it functions correctly (I would not have THOUGHT...

Excel: Date filter

add a helper column which contains the formula =DAY(F2) (where F2 is your date field). Dates are stored as serial numbers -- # of days since 1/1/1900. Sept 28, for example, is 41910, not 28/9/2014, so it doesn t begin with 28, it in fact begins with 41!...

Excel: Dynamic Cell reference.

Nabam, If the L001 cell you speak of is the cell that was found and contains rock breaker then Sub FindRockBreaker() Dim Rng As Range, sAddr As String Set Rng = ActiveSheet.Cells.Find(What:= Rock breaker , _ After:=ActiveCell, _ LookIn:=xlFormulas,...

Excel: Excel-2007 macro for conditional adding

So if I5 is 4, L5 is still 1 due to the start of the list. If the next instance of K5 doesn t occur until K10, then the value of L10 is I5+L5? Then if it doesn t occur again until K50, the value in L50 would be I10+L10? I ll see what I can do. If the...

Excel: excel formulae

aamir, Whenever I write a formula in the formula bar, I expect to get the formula in the cell and the results of the formula displayed in the cell (the formula is evaluated by excel and the results displayed). If you instead see the formula as a string...

Excel: excel over/under formula

If you really want to do this with two cells, you can just use the IF function to display the value IF it s over/under the other value. For example: C2: =IF(A1 B1, A1-B1, 0) D2: =IF(A1 B1, B1-A1, 0) See this lesson for more details on the a href= http://599cd.com/site/courselist/excel2010/expert/x3/?key=Allexperts...

Excel: Extracting Data from SQL server

Hi Brian, Can you specify the part where you need help? Whether you are having trouble connecting to the database or getting the date of is it with the accuracy of data or excel handling of the data. Depending on your response we can see if this question...

Excel: IF(and formula)

Chris Mitchell, You just list all the conditions inside an AND() =IF(And(D12 46,D12 49,F12 48,F12 51), remark , no remark ) Your description used less than and greater than so I have followed suit above. This would require D12 to be 47 or 48 and...

Excel: Formula to Calculate Cost based on Slab Rate

Hi Swastik, If I apply the exact logic in your problem description, I m getting different results. As per my understanding, you should get the answer as 7095 for 623 weight. Can you please double check. Please share the screenshot of formula you have...

Excel: Formula to calculate freight charge based on slabs (Min & Max Threshold Weight).

Swastik, Is it acceptable to build a table with the break out of the weight such as you show in your solution? If so, then could you send a sample workbook to twogilvy@msn.com so I don t have to guess at where you values are and where you want the...

Excel: formula/statement to check a cell

Well, I wouldn t replace the initial value, but you can compare two values and decide which one to use with the IF() function: =IF(D2 G2, D2, G2) See this video for details: a href= http://599cd.com/XLX3 IF FUNCTION /a Be sure to visit my web...

Excel: formula syntax

Christopher Mitchell, =IF(L4, aut 1 ,AVERAGEIF(B15:B1500,L3,AF15:AF1500)) should be =IF(L4= aut 1 ,AVERAGEIF(B15:B1500,L3,AF15:AF1500)) as you wrote it, if L4 is not empty or contains false, then it would return aut 1 , otherwise it would perform...

Excel: Generate List of EACH changes in other Sheet

Parantapkumar, So the code as written would do that now except it would list each event. You could write simple formula to identify those bad rows by comparing to the row above and marking those Rows. Then you could filter on the marks and delete those...

Excel: Increase Value of Cell by 1 depending on other cells Value

No, there is no formula you could put in your sheet3 to accomplish this. This can only be accomplished by employing VBA. A worksheet_change() event macro, for instance, is triggered automatically any time there is a change to a physical value (Not a formula...

Excel: Index-Match-Countif?

Josh, Your correct. I wasn t thinking that through. I was thinking of a different approach using array formula. This is tested on your data and worked for me: c2: =COUNTIFS(January_Data!$A:$A,$B2,January_Data!$B:$B,C$1)+COUNTIFS(January_Data!$A:$A,$B2,January_Data!$C:$C,C$1)+COUNTIFS(January_Data!$A:$A,$B2,January_Data!$D:$D,C$1)+COUNTIFS(January_Data!$A:$A,$B2,January_Data!$E:$E,C$1)...

Excel: invalid references

I would GUESS that the file is saving from the macro, but with errors, just not reporting them. As to freezing, you ve said the file is complex, so I would GUESS it would take some time to run - setting calculation to manual may help - I d also check if you...

Excel: invalid references

Not clear what the invalid references are referring to. I d suggest you go to Bing.com, type in A formula in this worksheet contains one or more invalid references (no quotes), and read through some of the responses to see if there s one which matches your...

Excel: invalid references

CHRISTOPHER Yes the invalid reference error message can be a bit difficult to find. I have a couple of workbooks that I get that message in that I haven t bothered to fix, becaue they are working fine and finding the error can be problematic and time consuming....

Excel: ISNUMBER function in Excel

Function ConvertNumber(arg As String) On Error Resume Next n = arg * 1 If Err.Number 0 Then ConvertNumber = arg if arg cannot be recognised as a number then return arg without change Else ConvertNumber = CDbl(arg)...

Excel: looking up information

1 - I don t understand your question; 2 - the layout doesn t match your description, For example, you have weeks 1 2 3 4 5 and item a and item b - what does that mean? Perhaps I could help if you sent a sample wb to me at bobumlas@hotmail.com. If you can,...

Excel: Macro to ungroup data

Gail, Not sure I understand the situation. So if rows 5-10 are in a single group you want to ungroup rows 5-10 and then group rows 6-10? Also grouping can be done as part of an outline or individually. In outlines, groups can be nested and so forth....

Excel: Max Value of rest Breakdown after Best Match

First, you description of the exact code being available is not well defined. In your sample results chart, every code in the left table has an exact match. So producing a 5 for 923 is inconsistent since 923 is in the right table (an exact match) with a...

Excel: Modifying a Macro

I can t see your workbook, so I have no context here. Are you still looking to track any change at all like before, but make sure the Account Number from column F is always recorded, too? I ve restructured the CHANGES TRACKER sheet so start with that...

Excel: numbers change format when hitting "Enter"

You may have a setting which puts in the decimal point. Look at File/Options/Advanced - near the top is a checkbox for Automatically Insert a decimal point - uncheck that. Also, if you format any cell to be currency, for example, then when you put in a...

Excel: Protecting certain cells

Gail, I followed what you say and it worked fine for me. The gray box in the locked checkbox means that some cells are locked and some are not. If you have all cells selected by clicking in the corner of the row and column headers, then if you uncheck...

Excel: Question

1) if your sheets are called sheet1 and sheet2 (as an example) you would need a formula - it would look like this =Sheet2!F24 Easiest way to create such a formula is to type the = sign, and then click onto the other sheet and the cell you want - excel...

Excel: Question

Hello Wing, Thank you for your question. We can do both of the things you want to do in Excel. There are several ways to do them, but I will give the way that seems the simplest to me, not knowing your level of expertise in Excel nor which version of...

Excel: Re: HELP! MS Excel (Search, Find, Replace and Extract Multiple Text)

Hi Dean, Find and replace can be easily handled. Even if there are multiple search and replace values, we may still be able to use a macro to do the find and replace efficiently. However, I feel that you are doing some steps which can be avoided and...

Excel: Row Height in Macro

I m not 100% sure that this will do what you want but Sub Insert_Blank_Rows() Application.ScreenUpdating = False Dim numRows As Integer Dim r As Long Dim rng As Range numRows = 1 Set rng = ActiveSheet.UsedRange For r = 131 + rng.Rows.Count To 131...

Excel: Very, very slow processing of Excel file

Hello Art, I have 2 options for you: 1. Try saving it in the latest format - .xlsx. Not sure if you have already done that. 2. If its only 10 tabs, you can try the following steps: 2.a Create a new blank excel file 2.b Copy the content from each sheet...

Excel: Slow VBA

Christopher Mitchell, I suspect most of the time is in the single command removeduplicates. There is no way to speed that up without performing the removal of the suplicates in vba. I won t suggest that but here is what I would do Sub ABC() Dim...

Excel: Time log

There s quite a lot of information I would need beyond what you mentioned, including definitions (what s batch time & site time & plant time, for example, and how does that figure into the information you want? And what would the graph be charting?) Also,...

Excel: vba to find and insert.

Nabam, first, in your example you say B49, but I assume you mean B50. this worked for me: Sub FindRockBreaker() Dim Rng As Range, sAddr As String Set Rng = ActiveSheet.Cells.Find(What:= rock breaker , _ After:=ActiveCell, _ LookIn:=xlFormulas,...

Excel: VBA tweak

Chris Mitchell, Sub gender() Application.ScreenUpdating = False With Sheets( Sheet2 ) .Visible = xlSheetVisible Sheets( Sheet2 ).Select Range( D2:D19569 ).Select Selection.Copy Sheets( PPI ).Select Range( C2...

Excel: Work Allocation macro

Navin, Seems like you could just do this by sorting your data into complexity, then using a formula to break you data into 3rds or 4ths or 5th or however many shift workers you have. So I have provided pseudocode that assumes the data is sorted by complexity...

Excel: writing formulas using code

Chris Mitchell, It depends on the specifics. If you mean you will removed many formulas and then put their functionality in a big macro/VBA code that will be run manually when the data needs to be updated, then having less work that needs to be done on...

http://en.allexperts.com/q/Excel-1059/2015/1/4-column-10-row.htm

Chris, You can send the workbook to twogilvy@msn.com I don t have a Macintosh or other apple product, so I will have to be able to read it in Windows Excel. I don t think that will be a problem if it is just a file with data and or formulas in it....

Excel: 6/17/14 Question

In Sheet1, range AA1:ZZ1, enter your numbers. Example: 3.7 in cell AA1, blank in AB1, -4 in AC1, blank in AD1, 1 in AE1, blank in AF1, 3 in AG1. In Sheet2, cell AA1, enter formula =MIN(COUNTA(Sheet1!$AA1:AA1),20) then copy this formula to Sheet2,...

Excel: Adding information in Excel

Roberta, when you click in your chart, at the vary top of excel, above the menu you should see a colored area in the center labeled Chart tools. Click on that. There are three sub menus. Design Layout format click on layout. In the labels...

Excel: altering VBA

Christopher Mitchell, Paste this code in a general module (Insert= Module) right click on your button, choose assign macro and select this subroutine: Sub Button_Click() Dim btn As Button, s As String, sName As String s = Application.Caller Set...

Excel: Altering VBA

Christopher Mitchell, I would guess that anything after the protect command is ignored. I would change the order to With ActiveSheet .EnableSelection = xlUnlockedCells If ActiveSheet.PivotTables.Count 0 Then _ .Protect...

Excel: Is there a conditional formula for shaded cell criteria?

Michele Debrey, there is no formula that reads the color of a cell. If the cell is colored based on a condition, perhaps that condition can be examined with your formula. As an example, say a row where the person is less than 10 is colored yellow. then...

Excel: convert number figures into words

Joe Excel provides a function for converting text to numbers, but not one for converting numbers to words. However, this can be done with visual basic code (which I am not expert with), here is a website listing that explains how to create the code for...

Excel: Copy Data from one cell to another cell in a different spreadsheet

Marie, assume both sheets are in the same workbook the value in Sheet1!B3 needs to appear in Sheet2!C3 in the cell Sheet2!C3: =Sheet1!B3 if they are in different workbooks, then with both workbooks open source: Myworkbook1.xlsx, Sheet1, cell...

Excel: Copy and paste macro

I find it difficult to see the data in images on this system- probably just my advancing years! As I said yesterday, I personally don t like using copy and paste within macros, not least because on macros that take a while to run, users are likely to be using...

Excel: IF/Countif(s) Formulas Help

1 - you re right, this is silly. 2 - #3 above says, in part, ..What cell is DIRECTLY to the right of Putts? C......3,8,13,18,23,28,...798... yet in your initial description you say Putts-----2-------1--------1--------1-------2--------2--------2------1--------2......

Excel: drive but not path changed in references

Debbie Spellman, Assuming he can t remap drive letters or anything, there is no restriction on typing in a reference to another excel file as long as it is visible and it should be visible if he has your file open. Also, you can change a reference by doing...

Excel: Excel 2010

the # signs indicate that the cell isnt big enough to display the data, but I ve never come across excel reformatting on its own - if it was a custom format and the settings for that format were changed, this would be a possibility, but my GUESS would be that...

Excel: excel formula

Hi Dennis, Do you have a sample copy of this file. Or a template of how you want it to look. I need to have a file to work with. In short, what I understand is that you should have a set of images in the second sheet, depending on the selection, excel...

Excel: excel formula

Yes, this is possible with VBA. 1) What is the sheet name and the address of the cell to watch? 2) Does that cell have a formula in it or are you manually typing in that cell? 3) What is the value to watch for? 4) What sheet should be jumped to once...

Excel: Excel formula to find ROI period

something like this =INDIRECT( A &MATCH(0,D1:D48,-1)+1) would do it, assuming that column D has the ROI data you mentioned, and the month number is in column A (question found in question pool, indicating original expert was unable or unwilling to...

Excel: Excel Greyed Out Copying Multiple Cells

Within the same instance of Excel, it is impossible to do what you describe. To do what you want you must open Excel two times and open each file in its own copy of Excel. To see whether Excel is open two times, open task manager, you should see two excel.exe...

Excel: Excel-Linking data from multiple spreadsheets

Hi Jenny, Thank you for the question. Based on my understanding of the problem you are experiencing, you do not need to use the IF statement alone, instead you can use dynamic linking with the IF statements. =Sheet1! January........... Please...

Excel: Excel Macros

Hi Niall. As far as I know, excel has a very good backward compatibity. If the macro is working on one office 2007, it should work on all office 2007 and above versions. The possible differences could include the following: 1. Security / trust center...

Excel: excel query, Excel, Conditional Formatting

Excel, Conditional Formatting: Please Google for Conditional Formatting in Excel. It is very hard to explain in text here. Basically, you can select a range of cells and add Conditional Formatting. There you have to mention a formula. You only need to mention the formula for the first row....

Excel: Excel - Timesheet

Ice Adobe has a feature that will allow you to convert the file to excel if you have that feature with your adobe then you can do what you want. With adobe open click on convert to excel and go from there. ---I have not had the need to use the feature...

Excel: faster search method?

Lee, look like you are using a userform (QualSearch.hide) and I don t know if you are doing exactly the same thing to each sheet and so forth but here is some general pseudo code that show looping through sheets and using the worksheet function countif...

Excel: Formula EXCEL 2010

so in A1 have the formula =if(isnumeric(C1), Closed ,if(isnumeric(B1), In Progress , )) then select cell A1 and apply conditional formatting. for your condition you could use a rule like =A1= Closed then format that to display Green then...

Excel: formula syntax

Christopher Mitchell, This formula worked for me. I if S1:S2500 is not on the same sheet as the formula that you understand you need to qualify it the sheet name. =INDEX(indexdatabase,MATCH(I2,$S$1:$S$2500,0),MATCH( target ,INDEX(indexdatabase,1,0),0))...

Excel: formula syntax

Chris, You formula returned 8 for me. I opened a new workbook and duplicated in the cells specified the numbers you stated. I copied your formula and put it in W2. It produced 8. if it isn t returning 8 for you, then I would look at the other cells...

Excel: formula syntax

Christopher Mitchell Countif only supports one condition. So you could do =COUNTIF(B2:AR2, 16 )-COUNTIF(B2:AR2, =10 ) so it counts the number of entries less than 16 and subtracts from that the number of entries less than or equal to 10. ...

Excel: Help Extending The Range Of An "IF" Formula

You say the sample formula is in B1:C5 (which I believe you mean C1:C5, since B1:B5 contains the text you mention). So, according to you, C1 contains =IF($C$4=1,$C$4*$C$3, ), C2 contains =IF($C$4=1,$C$4*$C$3, ), C3 contains =IF($C$4=1,$C$4*$C$3, ), etc....

Excel: help with lookup

siraj, try this formula: =INDEX($C:$C,MATCH( 50 deposits ,$A:$A,0)+MATCH( total line for eur ,OFFSET($A$1,MATCH( 50 deposits ,$A:$A,0),0,75,1),0),1) I look up to 75 rows past the 50 deposits line. If you it could be more than that, change the...

Excel: including dia.

assuming your data table in sheet2 contains all possible values, and that G11 contains the number followed by an x, then your formula sounds fine - you do only have 5 data values on sheet2 though? If it helps, my direct email (which would allow exchange of...

Excel: Insert Rates using formula

Nabam, I used the shaded data you show in you image. I placed it in the same location in sheet1 except that the orange shaded area was where I placed this formula: =IF(ISNUMBER(SEARCH( x ,G6)),VLOOKUP(LEFT(G6,SEARCH( x ,G6)-1)& mm dia ,OFFSET(Sheet2!$B$1,MATCH($G$5,Sheet2!$B:$B,0)-1,0,20,2),2,FALSE),VLOOKUP(G6,OFFSET(Sheet2!$B$1,MATCH($G$5,Sheet2!$B:$B,0)-1,0,20,2),2,FALSE))...

Excel: invalid references

Christopher, Here is one interpretation =if(and(c2 9,g2 9,count(b2:ai2)=4),countif(b2:ai2, 16 ), ) this says to show if either c2 or g2 are greater than 9 or there are more than or less than 4 numbers in the range B2:AI2. If all those conditions...

Excel: loop through nonbalnk cells and copy

Peter, See if this does what you want. Sub copydata() Dim sh As Worksheet, rw As Long Dim v As Variant, v1 As Variant Dim sh1 As Worksheet, ii As Long Dim i As Long, j As Long Set sh1 = Worksheets( Stuff ) rw = 1 v = Array( Data , Header , ...

Excel: Macro for lookup

sudharshan, You didn t give locations in terms of what cells, so I overlayed your picture starting in A1 so for Data 1, the first Lane_A was in cell A2 for Data 2, the first Lane_A was in D2 I started my output in G2 change the locations in the...

Excel: Money to Words

fixed Option Explicit Main Function Function NumToWords(ByVal MyNumber) Written by Philip Treacy http://www.myonlinetraininghub.com/convert-numbers-currency-to-words-with-excel-vba Feb 2014 Based on code from Microsoft http://support.microsoft.com/kb/213360...

Excel: Substraction from an absolute reference

I m not sure I ve got quite enough to go on here - in essence, it s along the lines of =if(Participant= Control ,control_value-Participant_value,Other_result) but I m not sure of the structure of the data you are working with - it if helps, you can email...

Excel: subtotal if text contains phrase

Sandy, This should do what you describe. ex. if data in column be contains the word Belief . there appears to be a typo in that line on what column to look at so I will show the example using column F but you can put in any column that contains...

Excel: Template Forum,

Good morning Katherine, Thank you for the question. Based on what I can surmise from the Customer Contact List image you supplied, this document uses filtering. As I do not have access to the Excel 2013 template file you refer to, I will be unable...

Excel: Transposing multiple colums with alternating data

Jermaine, Make a copy of your sheet with the data and run this macro against the copy: Sub ABC() Dim s As String, i As Long, j As Long Dim col As Long s = Left(Cells(1, A ).Value, 6) i = 1 For j = 2 To Cells(Rows.Count, A ).End(xlUp).Row If...

Excel: VBA Code

anthony, Anthony, Run a few tests: Sub Test1 Application.Workbooks.Open C:UsersAnthonyDesktopES900GSSample.xlsm End Sub If that works then in Sheet View, cell H28 I have: C:UsersAnthonyDesktopES900GSSample.xlsm Sub Test2...

Excel: VBA Copy & Paste Lookup

Hi Ben, If I understand correctly, you need to make sure the destination range is cleared up before pasting new data. Have you tried clearcontents command. You could select the destination range and clear the contents before pasting the new data....

Excel: VBA Copy & Paste using name lookup

Ben, So I believe you are correct. From the immediate window: set ocell = Range( A2 ) ? ocell.offset(1,5).address $F$3 Next is what you have ? oCell.Offset(0,5).Resize(Application.CountA(oCell.EntireRow)).Address $F$2:$F$17 This is what...

Excel: VBA solution to sort multiple cells in multiple rows

Sam, Select the rectangular area of cells you want to sort and run the macro: (do this on a copy of your workbook) Sub SortSelection() Dim rw As Range With ActiveSheet.Sort For Each rw In Selection.Rows .SortFields.Clear .SortFields.Add...

Excel: Visual basic

If my understanding is correct, that you have row 2, 4, 6, 8, 10, ... filled and rows 3,5,7,9,11,... are empty and you want the odd # d rows equal to the even ones, then no VBA is necessary. Select A2:D1975, then press F5, click Special , click Blanks, then...

Excel: Wage Formula

Hi Suzanne, Okay, for example let s assume the base rate is in cell B1, the fringe rate is in B2, and the fringe paid is in cell B3. Then the formula to calculate the prevailing wage, but limited to not less than 16.0 would be =IF(B1+B2-B3 16,16,B1+B2-B3)...

Excel: Attendance Tracking

Steve, I don t have a barcode reader but since the readers can simulate keystroke entry, I could write something that reacts to an entry in a cell. How about sending me you sample sheet and you explanation of what you want and I will take a look. ...

Excel: Bubble Chart

The part I can help with is getting the data into position on Sheet2 so your existing macro will still work with your changing data. This tweak will remove the unneeded 5 columns of data before running your bubblechart creation code. pre Public Sub...

Excel: Conditional format formula

Christopher Mitchell, You have the symbol in he wrong order =y3= 0 it should be =Y3 =0 a good trick when you have this type of problem is to enter the formula in a cell and Excel will usually suggest a solution although it won t always capture...

Excel: Copy paste some subsequent rows and some fixed rows

Hi Sofia, Here is my solution to advancing the rows by one on each button click: _______________________________ Private Sub COMMANDBUTTON1_Click() Application.ScreenUpdating = False Dim Ws1 As Worksheet, Ws2 As Worksheet, Ws3 As Worksheet,...

Excel: Date Color change

Shane, You can send me worksheet with just your dates in the proper cells and I will get it working for you. Send to twogilvy@msn.com Indicate which cells should be red now (today()). Sending an email would be pretty complex. Everyday for...

Excel: Dependant drop down lists

Jonathan, That would be an indicator that your dynamic named range formula evaluates to an error value. If you need me to debug the formula, then you can send an email with your workbook as an attachment to twogoilvy@msn.com Just putting the formula...

Excel: Excel 2000 Slider/Scrollbar

Hi again Charles, Here is a link to that speed control in my DropBox: https://www.dropbox.com/s/2arnlh0ya8lvi7h/Model%20Railroad%20Speed%20Control.xlsm?dl=0 Please let me know if this gives you any trouble. Damo...

Excel: Excel 2003 macro

Programming language hasn t changed from 2003 to later versions (aside from ability to do the new things in the program!) You didn t say how you find what the xxxx in Kxxxx. But if that is a known number in a variable, then you can just use this: Range(...

Excel: Excel 2007 VBA Autofilter Columns

Sam, It is not clear what you want. when I look at your code, you are looping from 4 to 63 and copying a 60 column Range fromm sheet A to D9, then to E9, to F9, to G9 and so forth. But when you paste into E9, you overwrite columns most of the data pasted...

Excel: EXCEL 2010

solay, You originally posted: A: D:TMC.TX MAIN ACTIVITIES 2015 B: D:TMC.TX MAIN ACTIVITIES 2015TMC.TX ACTIVITIES 2015 E: FEB2015 TMC.TX ACTIVITIES Since this will contain a macro, it must be named FEB2015 TMC.TX ACTIVITIES.xlsm C: D:TMC.TX...

Excel: Excel 2010: Line Chart Numbers are Odd

Ginger, send the data and the chart to twogilvy@msn.com and I will take a look. I really can t diagnose what the problem is without looking at the settings and examining the data. also state what version of excel you are using. -- Regards,...

Excel: Excel Display Issue

Before saving the file, use View/Arrange All/ and check the Windows of active workbook . Then maximize the window. THen save the file. No tips on creating compatible spreadsheets - but if your clients are using excel 2003, you should work in Excel 2003...

Excel: Excel formula

Hi again Peter, Sorry for not making it more clear. The code I gave you is itself a stand-alone function. It is not a macro, and should not be pasted into a macro, but rather pasted into a code module. The module can have macros, and even other functions,...

Excel: Excel Macro Doesn't Workd

1 - no idea. should open fine. But you might as well ignore that message since it opens OK. 2 - the statement ActiveCell = ActiveCell.Offset(1,2) simply assigns the VALUE of C3 to the active cell; it doesn t move it. To move it, there are lots of ways, the...

Excel: excel - quantity of odd and even numbers, in a range

Krystal, C1: 1-26 (cell formatted as Text so it doesn t get changed to a date) for the number of Even: =SUMPRODUCT(--(MOD(ROW(INDIRECT(SUBSTITUTE($C$1, - , : ))),2)=0)) for the number of ODD: =SUMPRODUCT(--(MOD(ROW(INDIRECT(SUBSTITUTE($C$1,...

Excel: filtering on dates

Gail, I would put in a dummy column. say your dates are in column F with a header in row 1 and data starting in row 2. say M is the next available column. In M2 I would put the formula =Day(F2) then drag fill that down the column. Then I...

Excel: formula syntax

Christopher, =IF(I5, ,2,3) should be =IF(I5 ,2,3) so the first argument is your entire condition. You might be mixing up how this is done in countif where the condition a separate argument from the cell reference. -- Regards, Tom...

Excel: Generate previous invoice based on invoice number

Hello Sofia, If I understand your question correctly, this code should do what you want. ____________________ Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = $H$13 Then Dim iRow As Long With Worksheets(...

Excel: (Import text file and Text2Column) in Loop

Sirajuddin, I can t test it, but I would use code like this: Sub Openfiles() Dim sPath As String, sName As String, sName1 As String Dim iloc As Long sPath = ThisWorkbook.Path If Right(sPath, 1) Then sPath = sPath & End If sName =...

Excel: Labelling in sequence

Pete, so there was another problem with the code. Should have been Sub abc() Dim l As String, r As Range, cell As Range l = Range( R2 ) Set r = Range( K1 , Cells(Rows.Count, K ).End(xlUp)) For Each cell In r If InStr(1, cell, /Player , vbTextCompare)...

Excel: lookup a value based on a second criteria

You could use an array formula for that: Assuming you have I2 in cell F2 and FR in G2: =INDEX($D$2:$D$7,MATCH(F2& | &G2,$A$2:$A$7& | &$B$2:$B$7,0)) Enter this formula using control+shift+enter. You will see {} around the formula in the formula bar...

Excel: Looping in macro

You didn t look thru all the sheets; you looked at the active sheet only and tried to delete rows 1:28 as many times as there are sheets! Change this: For Each ws In ActiveWorkbook.Worksheets Rows( 1:28 ).Select Selection.Delete...

Excel: Macros

Harold Buchanan, Hello. Try this: Sub MainPrint() select Case Range( I8 ).Value Case 1 PrintTeamOne Case 2 PrintTeamTwo Case 3 PrintTeamThree Case 4 PrintTeamFour Case 5 PrintTeamFive Case 6 PrintTeamSix...

Excel: Need a formula that includes ALL data in Column C

BL, the formula I posted was taken out of an excel cell and worked fine - so anything you say after that is pretty much meaningless to me. So if you want to send me your workbook I will take a look twogilvy@msn.com -- Regards, Tom Ogilvy ...

Excel: Pivot function

Rakesh, I don t think you can stop it from doing that. Given that, I added a column between the columns footnote and data. In that column, I just put in a list of sequential numbers starting with 1. I then added that in the same position in the pivot...

Excel: Rearrange row data under relevant column as per heading

Shiven, not much I can say. The formula works based on the information you provided. I can look at your workbook if you want to send it. If not, then there is nothing else I can do for you since my testing shows the formula working. send to twogilvy@msn.com...

Excel: Remove duplicate values in Excel

Eric, certainly a macro can be written to remove duplicates. However, in Excel 2007 and later, Microsoft added a menu item to remove duplicates. It is found under the data tab. I would explore using the built in capabilities of Excel to remove duplicates...

Excel: Replace text

Thiago Varella, It sounds like you have a sentence that contains both PRESS and PRESSAO such as A8: first word PRESS and second word PRESSAO so if you used your formula on that sentence it would return first word PRESSAO and second word PRESSAOAO...

Excel: sequential label in a column

Peter, could you send a workbook to me so I can take a look. It is hard to write code on a word picture? You can strip data that is sensitive and I will try to understand your requirement and give you a first cut . Then if you find that the code...

Excel: Sumproduct formula with zip code

I m working slightly blind as I ve got the formula but no data - but my INITIAL reaction (assuming that there should be results that match all conditions) is that the zip code cell is formatted as text NOT a number - so 98273 would then work. IF that doesn...

Excel: the today() function

You ve just discovered the underlying secret behind how date and time formats work. Dates are whole numbers. February 18, 2015 is the whole number 42053. Excel then applies formatting tricks to display that date any way you want, 100s of variations are...

Excel: Trigonometric identities in Excel.

Prashant To see the math and trig functions available----- with excel open click on the function key, in the insert function box/select a category/ select math and trig. All of the math and trig functions will be listed. I have not used graph or charting...

Excel: Trigonometric identities in Excel Application.

If you click the Formulas tab of the ribbon you will see a Math & Trig button, which lists all mathematical and trigonometric functions Excel has: ACOS ASIN ATAN COS SIN TAN are there. Since cosec = 1/cos the sec functions can eaily be mimicked...

Excel: Trigonometric identities in Excel Application.

Hi Prashant, I m not a hard core mathematician, but I have managed to find some trigonometric functions in excel. If there s and specific identity you are looking for, please let me know and I can try to help on that one. There is a mobile version of excel...

Excel: Trigonometric identities in Excel Application.

I m not a mathematician, so I don t use these functions - if you click the function button in Excel you can filter on the math and trig functions - this page lists them https://support.office.microsoft.com/en-us/article/Math-and-trigonometry-functions-reference-6f9459d3-0c22-42fa-9e84-218c11325ecd?CorrelationId=d7ee7e62-b36b-4bc8-a6b7-4847b199abe8&ui=en-US&rs=en-001&ad=US...

Excel: Trigonometric identities in Excel Application.

Hello Prashant, 1. Excel supports all the trigonometric functions, including hyperbolic trig functions, as well as their inverses. Excel does not directly support some of the functions you mentioned because these are not essential basic functions. Example:...

Excel: Trigonometric identities in Excel Application.

Hi Akerkar How are you? 2nd question, I do not know. But I believe that Excel (almost fully) is available for tablets and smart phones 1st question, Sin x ax nd Cos are easy to plot. Please try 1. x= 0, 45, 90, 135, 180, 270, 360 2. convert x...

Excel: Unicode Characters Data in Excel.

Prashant Sorry, I am not familiar with the ability of excel in this area. I suggest that you reask the question of another allexperts person and I m sure you can get a good answer. Sorry I couldn t be of more help. Richard Florida US...

Excel: Unicode Characters Data in Excel.

Hi Prashant, Yes, Excel can store Unicode-16 characters, not just the ASCII or Unicode-8 subset. They are stored as text, but can also be entered in General formatting mode. The easiest way to enter Unicode characters is to use the Symbols button on the...

Excel: Unicode Characters Data in Excel.

Hi yes you can save as unicode txt look this example that keeps utf8 unicode text format http://www.programarexcel.com/2014/03/guardar-archivo-txt-unicode-formato-utf8.html to save you must then select save as web tools then options then encoding,...

Excel: Unicode Characters Data in Excel.

slightly outside my area of knowledge, and not something I d be certain I d know how to research - were you looking to store the actual code numbers - if so, having the cell formatted as text would allow you to do this (to keep the trailing zeros). If you...

Excel: VBA plotting

Hi Felipe, I suggest you simply add a plot flag column to your sheet. In this column you can enter a value or flag in each cell to indicate whether you want the data in that row to plot (could be a TRUE/FALSE, 0/1, yes / no -- whatever you want). ...

Excel: VBA plotting

Felipe, I answered you first question and the database indicates that I did. I will paste that answer here as well: this worked for me (using your data) Public Sub BubbleChart() Dim BubbleChart As ChartObject, c As Long Dim lastcolumn As...

Excel: VBA solution

Felipe, this worked for me (using your data) Public Sub BubbleChart() Dim BubbleChart As ChartObject, c As Long Dim lastcolumn As Long If ActiveSheet.Name Worksheets( Sheet2 ).Name Then MsgBox Make Sheet2 the ActiveSheet, then run the macro...

Excel: VLookup on Multiple sheets, returning adjacent cell on same matched row

Hi Russell, If I understand your question correctly, if a match is found in the lookup you would like to then essentially repeat the lookup returning the value in column Z of the same row. It is also desirable to do this without the inefficiency of repeating...

Excel: 9x9 grid

The file can be downloaded from here: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/ Ignore the fact that it is setup as a 9x9 grid. Just focus on one section. Let s look at E1:E5, the New or Emerging Leader section. E1...

Excel: Altering VBA

Christopher Changed 41 to 501 where I have put in a comment ==== Sub abcde() Dim bk As Workbook, bk1 As Workbook Dim sh As Worksheet Dim r As Range, cell As Range Dim sName As String Set bk = ThisWorkbook bk.SaveCopyAs Filename:= E:Dummy.xlsm...

Excel: Altering VBA

Chris Mitchell, I would start with this: Sub adjustcategories() adjustcategories Macro Sheets( Categories ).Visible = xlSheetVisible Range( A2:A2000 ).Select Selection.Copy Sheets( Categories ).Select Range( A1 ).Select...

Excel: Assistance Required - Macro to Create Individual Workbooks

Bimmy, here is the altered code rC1.offset(0,3) is column D in the same row as rC1 Sub CreateBooks() Dim rCl As Range, rRng As Range Set rRng = Sheet1.Range( A1 ).CurrentRegion.Offset(1) Application.ScreenUpdating = False For Each...

Excel: Bubble chart for non-contigous range

Filipe, the problem appears to be in your loop For c = 1 To SelectionLength With BubbleChart.Chart.SeriesCollection.NewSeries Words = Selection.Cells(1, c) Words = Trim(Left(Application.WorksheetFunction.Substitute(Words, ...

Excel: Calculating test results

Novi It appears that what you need is a countif formula. This formula will count the number of incidents meeting the criteria within a given range. Lets assume your results of Pass, Fail, Fail and Retest, Pass With Caveat, Not yet tested, and unable to...

Excel: Converting text from a text string to number

Denny, for cell E21, this worked for me with all the examples you gave. =IF(ISNUMBER(SEARCH( - ,E21)),LEFT(E21,SEARCH( - ,E21)-1)*1,0)+(LEFT(IF(ISNUMBER(SEARCH( - ,E21)),TRIM(SUBSTITUTE(E21,LEFT(E21,SEARCH( - ,E21)), )),TRIM(E21)),SEARCH( / ,IF(ISNUMBER(SEARCH(...

Excel: creat a dropdown if previous cell =

it s actually quite tricky (as you guessed) - I ve only managed to do it using a helper column and an array formula. This version assumes that the A cell is A1 =IF(SMALL(IF(ROW(A1:A5)-1=A1,9999,ROW(A1:A5)-1),ROW())=9999, ,SMALL(IF(ROW(A1:A5)-1=A1,9999,ROW(A1:A5)-1),ROW()))...

Excel: How to create a running report

I suspect that recording a macro carrying out the actions you currently take would be a good starting point - though this would have fixed rows in place, you could then modify that to use variable rows. The question doesn t hint at the level of knowledge...

Excel: creating multiple files

Christopher, OK. Try this: Sub abcde() Dim bk As Workbook, bk1 As Workbook Dim sh As Worksheet Dim r As Range, cell As Range Dim sName As String Set bk = ThisWorkbook bk.SaveCopyAs Filename:= T:Dummy.xlsm Set sh = bk.Worksheets( Createfiles...

Excel: Creating PivotTable in VBA

sp, From what I see (and all I can see is your code) I would expect the code to error on the second run right here: shtDest.NAME = GapWalk_pvt on the second run, I would guess you are trying to create a duplicate sheet name since that sheet was...

Excel: Delete Blank Cells

Sub Update_Click() Application.calculation=xlcalculationmanual With Sheets( Data ) .Range( ak6:av25 ).Copy .Range( o6:z25 ).PasteSpecial xlPasteValues ======================= for each cl in .range( O6:Z25 ) if len(cl.value)=0 then cl.clearcontents...

Excel: Entering grouped drop down list in excel

Hello again Ibrahim, Okay, here is a solution that is based on using in-cell list data validation to provide the dropdowns. I could have accomplished this using ActiveX or Forms Control comboboxes, but I figured you would have mentioned these if you had...

Excel: Excel Question

One way is to filter the table on that termination date column, in which you leave all dates checked and uncheck the Blanks checkbox (near bottom of list). Then hit F5, click Special and select blanks. Then hit the del key. After that you can sort the table...

Excel: EXCEL VLOOKUP or Macro question

Charles, the file arrived, but I don t have anything that opens a rar file. Try just sending it as an xlsm or xls file. Having macros in it shouldn t make any difference (I have received plenty of files that have macros). If you want to compress it,...

Excel: Find and replace words in sentences excel

Hi Sofia, I m sorry, I m still not able to understand the question. I m not sure what you are trying to achieve with this exercise. If I can understand the end objective better, I ll be able to provide a better solution. If required, please drop me...

Excel: Formula inside a Loop

It s not the loop that s the problem, it s the use of a variable - you have a literal string sheet2!ai - which doesn t make sense to excel. If instead you have Range( Sheet2!A & i).Formula = =SUM(Sheet1!A & i & :Sheet1!E & i & )) you should find...

Excel: Goal Seek

Hi Ted, Thank you for answering my questions. Here is how to do it: Enter the following code into the worksheet s code module. To to this, right-click on the worksheet s tab, select View Code option, then copy and paste this code into the Code pane:...

Excel: GoalSeek

Hi Ted, Apologies for the delayed response. You can try using the Select Case commands in vba macros. Case 1 can be call, case 2 can be put and so on. If you can share the file, I ll modify the macro and send you the end product with both working....

Excel: Index and Match Error, is this possible?

Lee, first: Is there any way to get the match statement to take the first number that is higher than the entered value? from a general perspective, if the data is sorted descending for this column and you use the -1 as the third argument to match,...

Excel: IF INDEX MATCH RETURNS BLANK

Eric Your formula looks okay (without me knowing what the cell references in the formula have in them). I suggest that you check all of the referenced cells and make sure they have no value in them or that they are appropriate to the formula. Often times...

Excel: Iserror and vlookup function

Tejas, Is error is the condition passed to the IF statement. If vlookup can t find the value being lookedup, it returns a #NA error. Rather than display that error the formula is designed to show a zero. If if does not return an error, it means...

Excel: Match strings and copy to adjacent cell separated by comma

Hi again Sofia, This updated code should keep redundant keyword listings. _________________________ Sub CopyBasedonSheet4() Dim i As Long Dim j As Long, fn As Range Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet...

Excel: random golf pairings

Ben, You asked for a pattern that you could use and repeat. That is what I gave you - a pattern. There is no pattern that does exactly what you describe - your parameters don t fit into what you described as the desired output. So what I gave you was...

Excel: Run-time error '1004': using a Pivot Table

Steve Sorry, I don t see an issue in the code, but obviously there is one if its not working. Keep in mind I am a seldom pivot table user, so I may not be the best person to answer this. I suggest you reask the question of another and I m sure you can...

Excel: Sorting with VBA 2010

Range( B2:E20 ).Range( C2 ) is actually cell D3 (honest!) You want Range( B2:E20 ).Sort Key1:=Range( C2 ), Order1:=xlAscending, Header:=xlNo Further example: Range( F1:Z1000 ).Range( A1 ) is actually cell F1. The first part is the universe . In the...

Excel: Sum function

I think linking to a discussion on a similar problem will probably be best http://stackoverflow.com/questions/16262263/get-values-from-last-3-non-blank-cells-in-a-row This gives a number of solutions, so hopefully there will be one that you like and...

Excel: VBA for cell duplication based on dropdown selection

Private Sub Worksheet_Change(ByVal Target As Range) Dim rngChanged As Range, cellChange As Range Set rngChanged = Intersect(Target, Range( a1 ).EntireColumn) If rngChanged Is Nothing Then Exit Sub Application.EnableEvents = False For...

Excel: vba insert pics multiple times

Eddie, I have to assume that column A is wider than your pictures and that the rows are taller than your picture height. Sub getpics() Dim R As Range, cell As Range, pic As Variant Dim g1 As Range, cell1 As Range, r1 As Range Dim wcell As Long,...

Excel: Your VBA tab password solution - tweak needed!

You can set a variable, like PWOK (password Okay) which is requested on file open ... something like: Sub Auto_Open() pw=Inputbox( Please enter password ) if pw= AllTabs or pw= SomeTabs then PWOK=true else PWOK=False End Sub both pw and PWOK...

Excel: VLookup pulls a near match

Ginger, Your formula is equivalent to VLOOKUP(J8,Sheet2!A:B,2,True) which the true/1 means first means a close match. so this statement is wrong: The 1 means I want it IF J8 matches exactly You formula should be VLOOKUP(J8,Sheet2!A:B,2,False)...

Excel: 2nd workbook to get data from 1st wb

Sorry but this HelpDesk does not format data in a way that makes it abundantly clear what things should look like. But from what I can discern, you should not VBA to summarize data from one sheet onto another. If you select the columns A:E and then choose...

Excel: Assistance with code

Bimmy, If you are literally doing A1: 1 A2: 2 A3: 3 then I assume you have two sheets already in the workbook. You code set ws = Worksheets(c.value) will be successful for 1 and 2 because Worksheets(arg1) allows an index number such...

Excel: Concatenate and paragraphs

Christopher, =Q17&CHAR(10)&Q18&CHAR(10)&Q19 would become =Q17&CHAR(10)&CHAR(10)&Q18&CHAR(10)&CHAR(10)&Q19 (Of course you will need to format with Wrap Text to see the effect. ) If I understand your question correctly. -- Regards,...

Excel: Data to be filled in SECOND workbook from FIRST workbook

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 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: 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...