In excel 2010, I have 2 spreadsheets, the first "Active Total Master Turn" has the data, the second, "Pending" has a reporting chart. A cell on "Pending" contains the following formula:
=COUNTIFS('Active Total Master Turn'!F:F,"RK*",'Active Total Master Turn'!H:H,"Painting",'Active Total Master Turn'!C:C,"
I have a workbook with multiple tabs that I update every other month with current data. I have one tab for data that I clear each time and copy the updated info into. (That info is copied from another excel spreadsheet, by the way.) On the second tab I have sumifs formulas set up to pull in certain data from the data tab. This setup has worked perfectly until today. I copied my data into the one tab but could not get the formulas to calculate. It looks like the formulas aren't picking up the data. I've checked to make sure the formulas are calculating automatically. I've retyped the formula. I've replaced the =. I tried naming the ranges I wanted the data to be picked up from. I've tried changing the format of the data - general, text, numbers, etc. I'm working in excel 2010
View 2 Replies View RelatedI am trying to apply icon conditional formatting in a cell. The cell contains the following formula: =VLOOKUP(D20,'owssvr(1)'!O:W,9,FALSE The formula results in a "2", "1", "0" or "-1" in the cell. The icon conditional formatting is not working at all (no icon appears). I have the conditional formatting setup as numbers Green 2, Yellow 1,0 etc based on value. If I delete the formula and just type in any of those numbers directly, it works. I have changed my cells to "number" and it still does not work.
View 3 Replies View RelatedI am trying to use a countif and formula but can't get it to work. This part of the formula works fine =COUNTIF(J:J,"
View 9 Replies View RelatedI have a column of numbers ranging from 0000-9999. They are not in order and multiple numbers are used more than once. I would like to know how many times the numbers in the range of 4000-4999 appear. I am trying to use the formula =COUNTIF(F4:F500,"=4????") or =COUNTIF(F4:F500,"=4*") but neither are working. If I do a single number like this =COUNTIF(F4:F500,"=41420"), it will count them for me.
View 9 Replies View RelatedHow to total 2 different criteria from the same row?
Here is one option I was trying but does not work COUNTIF((E5:AT5,"D12")+(E5:AT5,"LD12"))/18
Basically I want Excel to count up the number of times the value "D12" and "LD12" occur in the range E5:AT5 and then divide that total by 18.
I have it working to count only the "D12" but am unsure how to have it count 2 different criteria. I am using version 2010.
MS Excel Version : 2010
Attached File name : countif for a given month.xlsx
I want to a sum the given condition xxx / yyy according to the month in yellow cells.
MS Excel Version : 2010
Attached File name : countif for 2 conditons a given month.xlsx
I want to a count with given 2 conditions AA11 / BB22 and xxx / yyy according to the month in yellow cells.
Excel 2010 windows 8
I am in need of a formula to count filtered results. On Sheet1 (Job Flow) the user enters data continually . The most important is the Date in column D some measurements in columns N,O & P that gives a result as a code, example s15020 or c3005 in column Q. A vlookup is then pulls up a price for the code.
On sheet two Column A There is a list of all the codes. I need a formula that will give a count in column B for the amount of codes that was entered in to the data base "sold" for a date filtered time period, be it a week, month or year. In other words the codes in ,column A sheet 2, should reflect the amount of units for the time filter on page1.
I am working with an Excel 2010 workbook that has two worksheets in it. What I am trying to accomplish is I want the second worksheet to scan the first worksheet for a student's name, and count all of the instances that the student has a score less than a certain threshold (we'll say "5" for this example). I have tried using various combinations of vlookup and countif functions, but have not had much success. I did get it to a point where it worked, but only for the first instance of that student's name; it wouldn't continue searching the first worksheet for any other instances.
I have attached a sample workbook as a reference : Sheet1.xlsx
I am using Excel 2010. I have a spreadsheet where column A is for Quarter, column B is for Employee, and columns C-R are for Codes (Code1; Code2; Code3; etc. through Code15). One line might have no values in the codes columns, another might have values in only Code1, another might have values in Code1 and Code2, and another might have values in all 15 columns.
A B C D E F --> R
1 Qtr Emp Code01 Code02 Code03 Code04 --> to Code15
2 2012.1 Liz CER02 INS12 WKH15
3 2012.2 Jim PIN55 WKH12 WKH19 WKH23
4 2012.2 Jon
5 2012.2 Jim WKH15 WKH23
6 2012.2 Jon PIN55 WKH15 WKH12 CER08
The worksheet is named "ALLAUDITS" and the named ranges are as follows:
Quarter: =offset(allaudits!$A$1,0,0,counta(allaudits!$A:$A),1)
Employee: =offset(allaudits!$B$1,0,0,counta(allaudits!$B:$B),1)
CodeData: =offset(allaudits!$C$1,0,0,counta(allaudits!$C:$C),15)
On another worksheet I need to be able to count how many times WKH15 appears in the CodeData range within a certain Quarter. I tried
=countifs(Quarter,"2012.2",CodeData,"WKH15")
But that didn't work, and from what I can tell in Excel support, it's because the ranges are different sizes. I have over 6500 rows, so I'm trying to avoid having to name all 15 columns and then do a sumproduct on all of them, such as
=sumproduct((Quarter="2012.2")*((Code1="WKH15")+(Code2="WKH15")+.......)))
Calculation would take forever.
I could just create a pivot table based on the "allaudits" worksheet, but I can't figure out how to get the actual codes as the row headers (instead of Code1, Code2, Code3, etc.).
I am using Excel 2010. The template only has 5 working days in it, but I need to set 6 working days (Saturday is also working day). Wondering, how to get this working without spoiling the functions. Also the date format is in US , need to change to UK without disrupting the Formula/Functions.
View 12 Replies View RelatedIn Excel 2010, I can manually enter the GetPivotData function (and it works), but with Generate GetPivotData selected / enabled (in PivotTable Tools --> Options --> PivotTable --> Options), the GetPivotData function isn't automatically generated.
View 1 Replies View RelatedI am using Excel 2010 on Windows 7.
I copied (with 'Paste Link') rows from a master Excel file to a file that performs calculations. This file (and many others like it) need to be modifed by other people. My tester has informed me that the data is not updating between the files for her, although it does work for me.
When I check 'Data->Edit Links' it shows the status of the file as 'unknown'. If I click 'Check status' it is then OK, but the next time I look at it, it has reverted to 'unknown'.
1. How do I get the status to remain at OK?
2. Why do the links work for me, but not for my end users?
I have a macro to collate data from multiple workbooks to a single sheet. The folder path to the source files can be chosen thorugh the macro. It runs perfectly in my system running on Windows XP. But it doesnt run on Wondows 7 system. Both are using MS Excel 2010. In Windows 7 system, while choosing the folder path where source files are located, it says "No items match your search" (screenshot attached). I would want the macro modified so that it runs on any platform. The macros that I'm using is given below:
[Code] .....
Untitled.png
I have a filter on the Qty column but when I sort Z -A or A - Z it does not work. I have tried formatting as number but no success, I have never seen this before, why the sort wont work? I am using Excel 2010 in Win 7
View 5 Replies View RelatedI am using this macro below to paste pictures from a folder. This works perfect when run on excel 2007, however it only links image when this is run on excel 2010. How to eliminate the error.
Option Explicit
Sub alex_it_saving_lives()
'get the image folder path
Dim strImagePath As String
strImagePath = GetImageFolderPath
If (Len(strImagePath) = 0) Then
[Code] .........
In an Excel Pro Plus 2010 workbook on a Windows 7 PC, trying to use the "After" argument in a VBA .Find function and it's not working as expected.
I've got the following data in column D:
Row1: Title
Row2: Row2Data
Row3: Row3Data
And using this simple procedure:
Sub FindTest()
If Range("D:D").Find("Title", After:=Range("D1")) Is Nothing Then
Debug.Print "Not Found"
Else
Debug.Print Range("D:D").Find("Title", After:=Range("D1")).Row
End If
End Sub
My expecation is that "Not Found"will be returned, but instead I'm getting the row # (1).
If I change the value in D1 the procudure returns "Not Found", as expected.
If I change the find argument to look for "Row2Data" it returns 2.
I also tried changing the "After" argument to "D2" ... same result.
Finally, I tried using this format: "Range("D:D").Find("Title", [D2])" and got the same result.
I'm using Excel 2010. When I go into the Excel Options, to the Save option and try to type in a specific network drive in the Default File Location: box, I click OK and then it doesn't save the changes. I close Excel down all the way and then restart a new Excel session and it keeps going back to "LibrariesDocuments..."
It doesn't seem to be just related to Excel...having the same issue in Word and Access 2010 as well.
Using MS Excel 2010.
As I understand it hyperlinks set in a spreadsheet should work regardless of where you save and/or copy the spreadsheet i.e. as long as the directory location of the location / file the hyperlink is referencing doesn't change (notwithstanding, the user must have appropriate access rights to the relevant directory locations)
However, the situation I have is the spreadsheet was generated and saved to directory location A (intention being this would be the 'master' yearly template) and the hyperlinks set, all of which work when you open the spreadsheet in this directory location. But when I copy the spreadsheet to directory location B, which is intended to be the yearly 'register' location for the spreadsheet, none of the hyperlinks work? When I hold my cursor over a link in the copied spreadsheet, in directory B, I do not get a 'pop-up' path like I do in directory A and when I click a link I get an error message:
Microsoft Excel The address of this site is not valid. Check the address and try again.
As stated above the documents the hyperlinks relate to have not moved, they remain in the same template directory (i.e. the link path has not altered).
I have a long bit of code that at one point saves a Word document and then saves it again with a new name and the old (legacy) .doc extension.
This all works find when the user is running Office 2007. However, it errors out for using Office 2010. The reference libraries are all correct (as far as I know).
Code:
Dim myDoc As Word.Document
Dim saveAsName as String
saveAsName = "some text here" + ".doc"
myDoc.Save
myDoc.SaveAs fileName:=(saveAsName), FileFormat:=wdFormatDocument
Had been using this formula for almost 3 years, recently the formula didn't work properly as the range goes down halfway only instead of to the last data in the column. I'm using Excel 2010 now.
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
I'm working on a macro that does the following:
1. Pull data from the internet
2. Perform calculations on the data
3. Graph the calculated values
- As I run the macro, Excel dies in step 3, where I graph the calculated values.
- It works about 5% of the time and gives me the "Excel has stopped working" error 95% of the time - I can't isolate any difference in the attempts
- I've located the area in the macro where it dies, but I can't identify the exact line since it seems to shift around in this area
- It works perfectly fine when I step through using F8
- I've attempted DoEvents and Application.Wait to debug. Application.Wait for 5 seconds allows the macro to work about 50% of the time and increases in efficiency the longer I wait
- Steps 1 and 2 work perfectly fine (I've used that code in many macros), so I'm pretty sure the issue is in the below code:
I'm running Windows 7, Office 2010
Code:
'Chart variables
Sheets("Summary").Select
Dim ChartRange As Range
[Code]....
I have a pivot table that I want to update via VBA based on dates user inputs on an input tab. (Want to use the Between function)
The Date field is in the Report Filter section (as I don't want it in the row or column area), but when I click on the dropdown arrow, I don't get any option to filter the date at all. I've double checked that the dates in my datasource are formatted for a pivot table, but that doesn't seem to be the issue.
When I use code I've found on this and other sites, it also doesn't work. When I run it, I get the following error at the "If Date DateValue(PI.Name) < StartDate Or DateValue(PI.Name) > EndDate Then..." line
Error Message: Unable to set the Visable Property of the Pivot Item Class
Code:
Sub Test()
Const StartDate As Date = #1/15/2013#
Const EndDate As Date = #2/13/2013#
[Code]....
I am using Excel 2010. I am using a shared workbook that was originally created in Excel 2003. This workbook has a command button that is supposed to be clicked to open a userform. However, I cannot even click on the button when opening the shared form in Excel 2010. When the form is not shared, the button works fine.
I have no problems with the button when opening the shared form in 2003 or 2007.
Is there anything I can do to make the command button work while the form is shared and while using Excel 2010?
I am trying to hide the excel 2010 application, open a csv file on a network drive, show the excel application.
The problem is a "Downloading" progress indicator appears, during the workbook open event I assume.
Code:
Sub Macro1 ()
Application.Visible = False
Application.DisplayAlerts = False
Workbooks.Open Filename:="S:LOGT5.CSV"
Application.Visible = True
Application.DisplayAlerts = True
I have seen the similar progress indicator during the "Save" event too. How to hide this?
I need to write a formula in F35 that looks at C4 and if that cell has a # of 25 or greater it will then look at F4 and if that # is greater or equal to 95% it will return a count of 1 in F35. I know how to use the countif function but am stumped when I try to add in the function to look at c4 as a qualifier.
View 7 Replies View RelatedI am using Excel 2003
Cell B13 contains the row number of the first row of data on the sheet and B14 contains the row number of the end row of data. This is because the number of rows vary over time.
I'm trying to do a COUNTIF to test for duplicates in column R (contains integers) and I want to specify the range to count by referring to the value in B13 and B14 in the formula and so I have the following which returns FALSE for some reason even if the value in R19 appears more than once in the range ...
=COUNTIF(INDEX(R:R,$B$13):INDEX(R:R,$B$14),R19)>1
I posted an earlier question about using a countif formula with two sets of criteria, Below is the formula I was given but for the life of me I can not get it to calculate. I have created a drop down box for each agent at my work and a drop down box for pass and fail. I want to keep a running total if they pass or fail on an assigment. I have labeled my worksheet phone.
=sumproduct((Phone!G:G="Fail*")*(Phone!B:B="Smith, John*"))
I have previously used the following code to successfully pull out IE webpage source code for string manipulation.
Its a crude example to demonstrate the principle:
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public IE As Object
Sub Sample()
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
[Code] ......
However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"
The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......
The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.
Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?
Using :
MS Excel 2010
IE Explorer 8.0
