# Excel 2010 :: Counting Bank Cells Based On Changing Criteria?

Oct 30, 2013

I am using Excel 2010.

BTES worksheet:
acct# Oct-13 Nov-13 Dec-13
13245 850
12458 850
45864
12385 1500

MainList - F2= (current month)

Based on the current month (which I planned on typing in) on the MainList worksheet I need to count how many blank cells are in the table above. The information above is on a separate worksheet but I can change that in the formula. Every month gets a new column.

This is what I have but its not working correctly: =SUMPRODUCT(--(BTES!AD1:BQ18=F2),--(BTES!AD1:BQ18" "))

## Excel 2010 :: Counting Cells With Multiple Criteria On Multiple Sheets In Workbook

Aug 5, 2012

I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---

=SUMPRODUCT(COUNTIF(INDIRECT("'"&\$H\$1:\$H\$43&"'!\$R1"),2*(AND("'"&\$H\$1:\$H\$43&"'!\$E1">"'"&\$H\$1:\$H\$43&"'!\$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&\$H\$1:\$H\$43&"'!\$S1"),2*(AND("'"&\$H\$1:\$H\$43&"'!\$G1">"'"&\$H\$1:\$H\$43&"'!\$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&\$H\$1:\$H\$43&"'!\$T1"),2*(AND("'"&\$H\$1:\$H\$43&"'!\$I1">"'"&\$H\$1:\$H\$43&"'!\$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&\$H\$1:\$H\$43&"'!\$U1"),2*(AND("'"&\$H\$1:\$H\$43&"'!\$K1">"'"&\$H\$1:\$H\$43&"'!\$L1"))))

but it returns a value of zero each time. Clearly there is an error in the formula.

Here is some background:
-- \$H\$1:\$H\$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.

## Excel 2010 :: Bottom To Top Counting With One Criteria (Up To Blank)

Jul 7, 2014

find the attached excel 2010 file <Count LBP or UBP.xlsx>.

My requirement is if `LBO' is available (in Column A) then the formula should count the number of `LBP' available above the `LBO' upto the blank cell. If there is no `LBP' above the `LBO' then it should plot zero in Correponding Column B Cell. If 2 `LBP's available above the `LBO' upto balnk space it should plot `2' in Correponding Column B Cell.

As like `LBO' and `LBP' the formula should work for `UBO' & `UBP'. refer the attached excel file.

## Excel 2010 :: Changing Tab Color Based On Cell Value?

Mar 6, 2014

I am using Excel 2010 and trying to change/edit the color of the tabs in my workbook to turn green or red based on a y or n placed in a cell (the same cell on each tab). I have tried variations on several themes others have asked about as well for Excel 2007 and attempted to adapt them to fit my situation but none seem to work. Here's what I was starting with:

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Range("A1").Value
Case Is < 2.5
Sheet1.Tab.Color = vbRed
Case Is > 2. 5, Is < 4
Sheet1.Tab.Color = vbGreen
End Select
End Sub

The cell I'm using is F2 and my cell value is simply y or n. I realize the example above references numeric values and greater than/less than options, but I'm not sure how to correct this for my need.

## Excel 2010 :: Sorting Or Counting Based On Text Colour

Sep 10, 2013

I have a database of names in one column in excel 2010 and over time I have coloured the text of some of the names green to show that they have been added to a new online database. I want to be able to count how many of the names have been coloured so I can see if it matches with the total number already added on to the online database. I have over 800 entries so I don't want to have to manually count them unless I find I have missed any. Conditional Formatting is greyed out (it is a Shared document so that might be why).

in creating an IF (text is a certain colour) THEN 1, 0 or any others ways I can find the total in that colour. I only have two text colours in my spread sheet, black and light green.

Example of Table.JPG

## Excel 2010 :: Counting Cells With Interior Colors Set Via Conditional Formatting

Jan 3, 2014

How to sort out a way to count cells whose fill colors have been set via conditional formatting.

I'm sure that I was once able to use Chip Pearson's CountOfCF function back in the days of Excel 2003, as it worked around the 3 condition limit and used .ColorIndex which assumed that you'd only ever be formatting using the .ColorIndex values of 1 to 56, but I can't seem to be able to get it to work in 2010.

## Excel 2010 :: Extracting Name From A List Based On Criteria

Nov 15, 2013

I am using Excel 2010 on Windows 7. Here is a link to the worksheet I have a question about : Example.xlsx

The only two relevant sheets here are "TestScores" and "Area 1." What I am trying to do is copy names over from the TestScores sheet to the Area 1 sheet based their test scores. It is important to know that a passing score is anything 85 or above. If you don't pass Phase 1, you move onto Phase 2, and if you don't pass that you move onto Phase 3.

Let me first explain how the TestScores sheet works. Here is a screenshot of the sheet:

I copied in column C, the names, from a roster sheet. Columns A, B, and D all VLOOKUP information from the roster as well. You can ignore columns E & F. Column G is just a test name that is manually inserted. They are all in the same order since this is an example worksheet I am doing the program on before I input the real data. Column H is also manually inserted, with data validation to only allow values 0-100 and a few different relevant terms (MS, EXT ABS, N/E). These next two rows are the formulas in columns I and J, starting in row 3.

I=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(H3="","",IF(H3>=85,"N/A (PASS)","Need")))))
J=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(I3="","",IF(I3="Need","",IF(I3>=85,"N/A (PASS)","Need"))))))

What these formulas do is the following: If N/E, MS, or EXT ABS is in column H, it copies those over to the next two columns. If someone scores 84 or below, it says "Need" in the next column. If they score 85 or above, it says "N/A (PASS) in the next column(s). The user is meant to write over these to insert the phase 2 and 3 scores.

Here is the "Area 1" spreadsheet I am working on that has the problem.

First off, I created these formulas by comparing them to another post and replacing my ranges and criteria. In column A, it returns all the names of Team Members who passed in Phase 1 (have a score 85+ in "TestScores" column H). The array formula is below and it works great.

{=IFERROR(INDEX(TestScores!\$C\$3:\$C\$1500,SMALL(IF(TestScores!\$H\$3:\$H\$1500>=85,ROW(TestScores!\$C\$3:\$C\$1500)-ROW(TestScores!\$C\$3)+1),ROWS(A\$6:A6))),"")}

In column E I have a similar formula, except it returns the names of those Team Members who have "Need" in column I of "TestScores." This formula also works great. The formula in column D is just a VLOOKUP based on column E and works well, too.

{=IFERROR(INDEX(TestScores!\$C\$3:\$C\$1500,SMALL(IF(TestScores!\$I\$3:\$I\$1500="Need",ROW(TestScores!\$C\$3:\$C\$1500)-ROW(TestScores!\$C\$3)+1),ROWS(E\$6:E6))),"")}

Here is where my problem is. In column F, I want to return all the names of people who passed Phase 2, which means they have a score of 85 or above in column I of the "TestScores" sheet. However, not only is it not returning the right names, I can't even figure out what criteria the names is returns has. The current list, in (mostly, oops) red, is incorrect. The formula, below, is almost the same as the formulas above, with only 1 difference in each case.

=IFERROR(INDEX(TestScores!\$C\$3:\$C\$1500,SMALL(IF(TestScores!\$I\$3:\$I\$1500>=85,ROW(TestScores!\$C\$3:\$C\$1500)-ROW(TestScores!\$C\$3)+1),ROWS(F\$6:F7))),"")

It refers to column I, instead of H, compared to the formula in column A. Its criteria is >=85 instead of ="Need", in comparison to the formula in column E. Nonetheless, it still isn't returning the right names! The other weird thing, is if I replace the ">=85" with "=90" it will return the names of team members who scored 90.

## Excel 2010 :: Sum Based On Criteria In Specific Column?

Oct 20, 2011

how can i sum base on the Criteria in Columns G, in this sample the sum should be 67.

## Excel 2010 :: Conditional Format Based On Multiple Criteria

Apr 25, 2014

I would like to shade my cell yellow if the priority equals A or A-NEW and ACV=o.

In the data below, items 4 and 13 would be shaded yellow.

I am using Excel 2010.

Priority A, B, C

Consumer UPC

Item Description

ACV Weighted Distribution

[Code] .......

## Excel 2010 :: Extract List Of Names From Table Based On Two Criteria?

Mar 3, 2013

I'm trying to extract a list of names from an Excel 2010 table based on two criteria, thus:

=IFERROR(INDEX(Database[FullName],AGGREGATE(15,6,ROWS(Database[Age]-ROW('Database'!\$A\$2))/((Database[Age]>1.8)*(Database[Age]

## Excel 2010 :: Highlighting Rows In VBA Based On Multiple Criteria And Sum Function

Apr 25, 2014

I am trying to write a macro in VBA excel 2010 that compares 2 sheets.

The macro should be something along the lines of if column 7 on sheet 1 = column 1 on sheet 2

AND

on that same row if column 6 on sheet 1 = column 3 on sheet 2

highlight green

** also on sheet1 there can be the same batch ID so if it is the same batch ID it needs to calculate the sum and look at that amount...

Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID

475-T
03/03/2014
Connect
\$562.95
\$19.95
\$543.00
6G000001450835

[Code] .......

Results >

Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID

475-T
03/03/2014
Connect
\$562.95
\$19.95
\$543.00
6G000001450835

[Code] ..........

## Excel 2010 :: Pulling Data Based On Multiple Criteria With Duplicate Values

Mar 14, 2012

I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.

I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).

For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?

Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250
Payment612345678750Vacation798765432800Salary898765432250
Payment998765432100Overtime1098765432450Salary1198765432300Overtime
Sheet1

Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432
Sheet2

## Excel 2010 :: Return Positive Or Negative Value Farthest From Zero Based On Criteria From Another Range

Mar 11, 2014

I am creating a template in Excel 2010 and am having some issues due to a combination of positive and negative values. Specifically, I have a named range of values that can have either a positive or negative deviation from zero. I need to return the value that has the greatest deviation from zero (either + or -) based on criteria in another named range I have used the following array formula with success for the "Y" range:
{=IF(PRB=M1,INDEX(Y,MATCH(MAX(ABS(Y)),ABS(Y),0)),MAX(Y))}

However, if there are not any negative values, as in the "Z" range, it only returns MAX(Z) for the entire range, when I would like ti to return "0.00134" (the largest deviation from zero based on the PRB range criterai. Would this be better accomplished using VBA?

PRB
Y
Z
A90B90
A90B-90

[Code].....

## Game Points Bank Account (running Total For The Bank)

Nov 5, 2009

Sheet6

80You Need 18,000 Pts.IntermediateYour Credit is 0 Pts

Excel tables to the web >> Excel Jeanie HTML 4

i have a "Game Points Bank Account" that keeps track of the points users earn or spend in the game...what i need help is creating a running total for the bank..ive tried everything nothing works..

(this is actually like a traditional bank account)

each player starts with 10,000 points

*Row 8 is the only row to have formulas*

*Rows 9-11 are typed in to show my desired results*

Here is the point value "Ranking System" for Column T

Sheet6

Excel tables to the web >> Excel Jeanie HTML 4

## Excel 2010 :: Perform Sum Based On Other Cells Content?

Jan 31, 2013

Exel 2010 cells formatted to Time

need to look at h1 and h2 if no data ignore

other wise add data together if total exceeds 08:45

then deduct 45min from h1

if combined the are less than 8:45 then h3 must equal 8:00 by altering h1 only

Example

h1= 5:00
h2= 2:00
h3= 8:00

h1=
h2=
h3=

h1= 8:00
h2= 2:00
h3= 7:15

## Excel 2010 :: Shade Range Of Cells Based On Particular Cell Value?

Feb 17, 2013

I'd like to shade a range of cells based on a particular cells value.

Specifically:

If cell E3 is JUNE then I'd like the range of cells from B10 thru I10 to be shaded.

If E3 is JULY then it would shade C10 thru I10....etc.

Excel 2010

## Excel 2010 :: VBA Repeating Formula(s) In Specified Cells Based On Defined Variables?

Dec 28, 2012

In my worksheet, I know the last line of my report AND I know how many "reporting segments" (all equidistant and identical) there are in the report based on the last row using the below (which I tested in several reports in messages):

VB:
Dim vListEnd As Variant
vListEnd = ActiveSheet.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row
vTaskCount = ((vListEnd - 5) / 22)

My "reporting segments" are ALWAYS 22 rows each and the first one always starts in row 5. Example: A document where the last row (containing an End of Report special character) is 247 would have 11 reporting segments.

Within each reporting segment, there are two different formulas needed.

For the first reporting segment located in rows A5 thru J27:

I need FORMULA1 in all cells of the range (D5:D15 dragged out to H5:H10(not 15) and D18) and FORMULA2 in cells (F20:F22,J20:J22).

Then I need to repeat that vTaskCount number of times (number of segments) and at intervals of 22 (segment repeater offset). Some reports may have 2 segments, some might have 50, but the structure is always identical.

My formulas are all pretty simple if it makes a difference (originally using FormulaR1C1 = "=RC[-1]+RC[-3]" --- but because I now know solid cell locations I suppose I don't need to make them relative, too.)

## Excel 2010 :: Delete Duplicate Rows Based On Values Of Cells

Jun 17, 2014

I'm new to VBA and macros, using Excel 2010, and am trying to figure out how to delete all duplicate rows in a sheet where 2 or less of their values in column A is "1". I'd like have a script that is flexible enough to change to 3 or less if need be. I also have a header row that needs to be offset in the process.

A---B-
0--123 <-delete
0--123 <-delete
0--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321

or

A---B-
0--123 <-delete
0--123 <-delete
1--123 <-delete
1--123 <-delete based on this the value of column A
0--123 <-delete
0--123 <-delete
1--321
1--321
1--321
1--321
1--321

## Excel 2010 :: Input Multiple Cells Based On Master Cell?

Dec 9, 2011

I am trying to create a UserForm in Excel 2010 that will look for a part from our database (or on a specific Sheet, possibly a hidden sheet), then add specific data from that part's row into a new line.

More Specifically:

| Part # | Description | Category | SubCategory | Cost | Labor Hours | Weight | Etc |

I want to be able to input the Part #, and have it automatically add specified information to sheets 1 and 2 and those two differ. 1 is our Quote Sheet, and 2 is our bill of materials.

Also, I need each piece of information to go to a specific column (ex. Cost always goes to Column K)

What I have now is a broken UserForm that references a DOC file with a Table, but I need something a good but more complex.

Ideally, what I want is to have 4 ListBoxes where you choose Category --> SubCategory --> Make --> Model then have several CheckBoxes below that would determine whether to add a part (Cost, Labor, Weight).

The reason for this is because we have no need for the Cost or Labor Hours in our Bill of Materials, but need that information for our Quotes. And we generally don't need Specific Dimensions in our Quotes, but DO need them in the Bill of Materials...

Can I make it so that it will insert Pre-Specified information into both sheets at the same time?

## Excel 2010 :: Macro Based Search Function Using Data From 2 Cells

Mar 15, 2012

I have been using a macro to search and highlight customer addresses for me, however I have changed my worksheet and now have the addresses in two columns instead of one (D for the numbers, E for street names) for sorting purposes.

What I would like to do is either; find a way to search using data from 2 cells to get a result, or have multiple results highlight and popup.

Using: Excel 2010

Code:
Sub SEARCH_FUNCTION()
Set FoundCell = Sheets("Route").Cells.find(What:=Sheets("Intro").Range("G15"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
With FoundCell

[Code] ...........

## Excel 2010 :: Changing Last Name First Name To First Name Last Name?

Aug 26, 2012

Is there functionality in Excel 2010 to convert a cell that has last name, first name to first name last name? For instance:

Before: Mouse, MickeyAfter: Mickey Mouse

I'm trying to merge data from 2 sources, and the common data between the two is the name. However, it is formatted differently in each data source.

## Excel 2010 :: 3+ Conditional Formatting Rules For Cell Range Based On Formula From Other Cells

Feb 5, 2013

how to apply conditional formatting via VBA to a range of cells based on input from another range of cells. Obviously this would be easy in Excel 2010, but I'm still using 2003 at the office and it needs to stay in this format to be readable by other users:

For cells M8:EK8, my conditional formatting
condition 2: Formula Is =AND(\$E\$8>=M2,((\$E\$8-\$D\$8)>=(N2-\$M\$2))), color index is 40
condition 3: Formula Is =AND(\$F\$8<=M2,\$G\$8>=M2), color index is 39
condition 4: Formula Is =AND(\$H\$8<=M2,\$I\$8>=M2), color index is 40
condition 5: Formula Is =AND(\$J\$8<=M2,\$K\$8>=M2), color index is 39
and so on

The cells in the range M8:EK8 are blank, they only get colored based on input added to D8 to K8. If there is no input, then the cells should be uncolored.

resource tracking ex.jpg

## Excel 2010 :: Macro To Color Code Cells Based On Value In A Cell And Range In A Table

Dec 2, 2013

I am using Windows 7 and Excel 2010.

Is there a way to create a macro to color code a cell based on the value in a cell, and then look up a value in a table, then color code it based on where it fits into the table?

I have a table of values for about 30 projects. In column g - there is a CPI value (see bold column)

Example: Project ID
Name
Program
PMT
SI ID
AC
Milestone
TCP Level
[Code] ......

Here is the table:

I have to color code a cell, base on the CPI and how it fits into the table below. So if the current Milestone is M2 or M3 and the CPI calculated is .14 the cell would be colored RED, if the CPI number is 2.01 for M2-M3 I would want cell to be colored Turquiose. If we were at Milestone M6 and the CPI was 2.01, it would be colored blue. If the CPI was .75 at Milestone M5, it would be colored Green

LEGEND
Earned Value Limits

Milestone
RED
Yellow
Green
Turquoise
Blue

M2-M3
2.15

M4-M6
1.66

M7-M11
1.26

## Excel 2010 :: Filtering Dates And Counting Occurrences?

May 28, 2014

I am using Excel 2010, and trying to count the number of times a certain name occurs within a specified date period. The date range start date is manually entered in F7, and the date range end date is manually entered in F8. I need to be able to choose different reporting periods. The answer to this question goes into F3.

I also want to know how many working days (Column D) it takes per name. A working day is Monday-Friday, less public holidays. In this period there have been public holidays from 24 December until 2 January (inclusive), 20 January, 6 February, 18 April, 21 April, and 25 April. I put these dates manually into the worksheet "Holidays".

For the number of times a certain name occurs I tried the COUNTIFS function but keep getting a "zero" value. I used the NETWORKINGDAYS function for the working days and have incorporated the holiday periods. That seems to work OK and I can live with the negative date values showing here.

The really tricky part for me - can I combine the COUNTIFS (assuming it works) and NETWORKINGDAYS functions into a single function to only count the working days of a specific name. As some names have yet to be completed (i.e. there is no data in Column C) how do I not count those? The answer to this question goes into F4.

There will be about 6 of these spreadsheets, and I intend to link all worksheet data into a "master" spreadsheet which automatically updates whenever it is opened. The separate spreadsheets are stored in a document management system rather than a network drive so I can't state a filepath.

## Excel 2010 :: Counting HTML Checkbox Values

Feb 1, 2013

I am copying a large table of data from a report generated in Firefox and pasting it into Excel 2010. The data has several columns of html checkboxes. I need to do two things with the checkboxes and would like to do a third:

1: Count how many checkboxes are ticked in each of the columns.
2: Compare a column A of checkboxes to a column B containing numbers, and then both count and highlight any row where the checkbox is ticked but column B is a 0.
3: (optional) I would like to erase the html checkboxes and, if the box was checked, replace it with a regular x in the underlying cell.

I found some code on another forum that generates a list of values for each checkbox (vba - Obtain the value of an HTML Checkbox inserted in Excel worksheet - Stack Overflow).

Based on that, I recorded a macro to extract the html Name of a single checkbox and then set up a Vlookup for the True/False value. However, I can't figure out how to automate a vlookup for every individual checkbox and put the data in the appropriate underlying cell.

## Excel 2010 :: Changing Legend Keys

Sep 18, 2012

is there a way to change the legend keys (aesthetically) in 2010?

I created a pie chart using a template but I don't want to keep the defualt legend keys. I tried changing the theme but it appears that the legend keys that come up are the same no matter what theme I change it to.

The Chart is a 3d Pie chart, I think that matters because the regular square legend keys show when the chart isn't 3D.

## Excel 2010 :: Autofill Ever Changing Range?

Jan 23, 2013

Excel 2010

I have this code in a macro, the range will change as more data is added. so that I dont have to keep changing the range. How can I have this code autofill from the activecell to the last cell that has data in column M.

Selection.AutoFill Destination:=ActiveCell.Range("A1:A50000")
ActiveCell.Range("A1:A50000").Select

## Excel 2010 :: Web Query Using Changing Filename

Mar 6, 2014

I'm using excel 2010, looking to import a .xls file to a tab from a web query. Herein lies the problem: the .xls file is named based on the current date. So for instance, xLfile03.05.14.xls. Which changes the next day to xLfile03.06.14.xls. This is not a local file but one on microsoft sharepoint. It's ONLY available through sharepoint.

## Excel 2010 :: Changing Format Of Dates In A Column?

Oct 19, 2013

I am working with a 2010 Excel spread sheet and need some direction.

One column has dates in it. Some dates are like dd/mm/yyyy (eg 15/03/1974) and others in the same column are in the following format 14th October 1983.

I need all of the dates to be in the first format (dd/mm/yyyy).

Is there a way to do this without manually changing each field? I have already tried highlighting the column, then clicking the 'numbers' arrow and picking 'date' from the number tab but that didn't work. It's never that easy, is it?

## Excel 2010 :: Changing Default Text Box Settings?

Sep 5, 2012

When I place a text box in Excel 2010, the default is for everything to be locked (Box is locked AND text is locked). I want to change the default setting so that when I start Excel fresh, anytime I place a text box the "LOCK TEXT" box is UNCHECKED.

There's a place where you can select "Set as default text box") after you make the changes, but it's not sticking (even in the same document without closing. IE, I make the changes to a text box, then click the option that says "Set as default text box", place a NEW text box immediately in the same document but the text is still locked.