Automatically Rank Cells Ignoring Hidden Rows?
Mar 7, 2012
Any way to automatically "rank" cells, ignoring any hidden rows? I don't even really need Excel to sort for me (although I wouldn't be opposed to that), but I'd like the first non-hidden cell in column A to always say "1" and the second one to say "2" and so on.
The back story, if you need it: I have a spreadsheet that tracks sales for 2 separate brands. I've created a macro and linked it to a listbox, so that you can choose which brand you want to view and Excel will hide all rows that aren't pertaining to that brand (I'm absurdly proud of that accomplishment). The only problem is the rankings are hard-coded, so when you hide one brand the rankings no longer make sense.
Every Monday I sort the items by the most recent week's sales and rank them from there. The addition of a second brand is obviously new to my little spreadsheet!
View 1 Replies
ADVERTISEMENT
Feb 18, 2009
I posted a thread a while back about how to ignore hidden rows from autofilter when using formulas. http://www.excelforum.com/excel-work...ml#post2039071
The solution worked perfectly. But I have since had to use this on another sheet which is laid out a bit differently, I have tried everything to try and modify the formula to work on the new sheet but so far I have failed miserably!! I need to do the exact same thing on this sheet - filter the sheet by week and search the data for certain criteria ('Line' and 'Fail Reason') and sum up the total quantity, while ignoring autofilters hidden rows. Here is an example of the sheet, along with an attempt of mine to modify the formula, which I think I have got TOTALLY wrong!
View 5 Replies
View Related
Aug 30, 2013
I need to average the columns of data and ignore both hidden rows and zero values. I have tried writing if statements as well as the subtotal function. Both functions either ignore null values or hidden rows but not both.
The system wont let me update a sample workbook but Im wondering whether there is a formula or combo formula for this.
View 4 Replies
View Related
Sep 12, 2013
I'm trying to rank the values in row 94 while ignoring only zero values and including positive and negative values. I'd like the function to rank the values in descending order, beginning with the most positive value and ending with the most negative value while ignoring all zeros. The function I've come up with so far ranks the positive values correctly and ignores the zeros, but the negative values are ranked as if they come after all of the zero values instead of being ranked immediately after the smallest positive value. Is there a way to fix the function below to make it do what I want?
=IF(D94=0,"",RANK(D94,$D$94:$AY$94,0))
View 9 Replies
View Related
Sep 8, 2009
Basically I have recently started my own personal music chart and I'm having problems with a Year To Date page.
Basically all I want to know how to do is how to lock some cells together and rank many rows of those cells with 1 column where the total is?
I have attached the spreadsheet as a zip files as I was having problems uploading it as a xls file.
View 13 Replies
View Related
Feb 28, 2014
I am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book.
View 7 Replies
View Related
Jun 19, 2009
I'm trying to create one spreadsheet where I use formulas to come up with an "end total" on a daily basis. I want these "end total" values to be automatically recorded each day on a separate spreadsheet that keeps a historical log.
In example:
On Day 1 I will enter numbers into "Spreadsheet A" to come up with an "end total" for that day. This number will automatically be entered into "Spreadsheet B" on the first row.
On Day 2 I will write over the numbers from the previous day in "Spreadsheet A" to come up with a new "end total". This new "end total" will be automatically entered into "Spreadsheet B" on the second row.
View 10 Replies
View Related
Jun 15, 2009
I have a column with with Employee ID numbers, with anywhere from 2-5 blanks rows in between them. In another sheet I want a column that returns these ID numbers without the blank rows in between. All of the ID's begin with "N", for example:
I have:
N33333
(blank row)
(blank row)
N22222
(blank row)
(blank row)
(blank row)
N66666
I want:
N33333
N22222
N66666
View 9 Replies
View Related
Mar 23, 2012
Does Excel have the ability to automatically hide rows without values in certain cells?
For example:
Sheet1
ABC1Route NumberTechnicianNumber of Units Cleaned25Tony237Don749 511Ray12613James16715Chuck21817
In this example, Rows 4 and 8 would automatically be hidden, leaving the other rows displaying. Of course, somehow I'd have to "Unhide" these rows at some point to add data if needed.
This is for a spreadsheet that is about 500 rows. Conceptually, I would automatically hide the rows w/o data in column B, analyze (or print) the worksheet, then "Unhide" the columns to enter data the next day.
View 2 Replies
View Related
May 1, 2009
I use the code below to check and see if a sheet exists or not when a workbook is opened. If it does not, continue. If it does I need to run a different sub on it.
Twist, I need to check for a hidden sheet. How would I chg the code to do this?
Private Sub Workbook_Open()
'Also need to check the code in the userform. Has On.Time command.
'SplashForm.Show
Sub Sheet_Test_1()
Dim sh As Worksheet
On Error Resume Next
Set sh = ActiveWorkbook.Sheets(" total")
If Err.Number <> 0 Then
MsgBox "The sheet doesn't exist"
Err.Clear
On Error Goto 0
Else
MsgBox "The sheet exist"
End If
End Sub
View 5 Replies
View Related
Mar 25, 2012
I am trying to conditionally format the top middle and bottom thirds of a range of data. Problem is, that the range needs to be flexible as sometimes there may be a maximum of 36 cells with data, but sometimes there may be less (so there are blank cells in the range that need not be counted). The methods I have tried always include the blank cells, and so it is not equally formatting the thirds (as it includes the blanks cells as part of the bottom data)....
Here are the 2 methods Ive tried so far using excel 2003)
Top 34%:
=IF(INT(COUNT($D$3:$D$38)*34%)>0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*34%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*67%)),MAX( $D$3:$D$38))0,LARGE($D$3:$D$38,INT(COUNT($D$3:$D
$38)*100%)),MAX( $D$3:$D$38))
View 4 Replies
View Related
Feb 26, 2008
Suppose in cell A1 I type in the number "100" and in A2 I type in the number "5"
What I want to do: Have the number "100" repeat 5 times in a vertical list (so 100 appears in B1, B2, B3, B4, B5)
View 2 Replies
View Related
Oct 8, 2008
I'm trying to craft a gradesheet for my son's teacher and I am runnning into a problem obtaining a students average. In one row I've listed the possible questions for all the tests whether the test has been taken or not. In the row below I've listed "questions answered correctly" for only the tests they've taken. I need a way to calculate the overall avergae of the student while ingoring columns with null values in the "questions answered correctly" row.
I know there is a way to ignore zeroes by excluding them with an IF statement but I didn't want to do that in case one of the students actually did get a zero. As the attached example shows the simple sum function renders a 25% average while the average should actually be 50%.
View 2 Replies
View Related
Dec 11, 2008
I have a set up of investments. Say A1 through to G1. Then on the next row the investments range from A1 to G1 again. This continues down many rows. There are spaces such as D1 and D2 through E2.
A
B
C
D
E
F
G
1
ausbil........................
Starting in column H, I would like to list the investments but ignore investments that have already appeared on the row (ie list only the first occurence) and ignore the blanks as well. For example, D1, E1, F1 are to be ignored because of the blank, and the fact that ausbil and amp already appear.
In row 2, the blanks in D2 and E2 are ignored as well as the repeated 'amp' in F2. So the table should look like below from column H:
H
I
J
K
1
ausbil
amp
vanguard
bt
2
amp
becton
fidelity
bt
Is there a simple formula for this? It has to be in rows as I have at least 60 columns of data before these columns begin. There are at least 300 rows.
View 9 Replies
View Related
Sep 25, 2012
How do I delete filtered rows without deleting the hidden rows in excel 2010?
View 8 Replies
View Related
Mar 4, 2014
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
Attachment 301726
View 4 Replies
View Related
Apr 18, 2013
I have data in B4:B55 and need a formula to return a count of rows, including rows that are blank. However, there are hidden rows that need to be omitted from the count.
View 1 Replies
View Related
Jun 2, 2009
i have a certain column that tells me if a client has withdrawn from the company, and the column just says "y" or "n" i want excel to automatically copy the whole row to another sheet if that cell is a "y" for yes the client withdrew...
View 9 Replies
View Related
Aug 8, 2014
I want to count back the last 6 cells in column S which has data (ignoring any blank cells) and add them up.
My data is ranging betwen S62:S143 (with S144 being the cell that I want to show the total for the last 6 cells with data). I have column headings and various other bits of data from S1:S61 that I want to exclude completely but need to keep for historical purposes.
Here is an example of some of the data in column S:
56
BLANK CELL
BLANK CELL
BLANK CELL
45
34
BLANK CELL
BLANK CELL
37
BLANK CELL
BLANK CELL
42
BLANK CELL
46
49
BLANK CELL
BLANK CELL
36
35
This is my formula so far:
=SUM(OFFSET(S62,0,0,COUNTA(S62:S143)))
I have tried adding a negative and positive number on the end of the COUNTA formula but all it does is count all the cells within that given heigh range, not a specific number of cells with data.
I don't specifically need to use SUM. As long as I get a total.
View 3 Replies
View Related
Aug 20, 2009
I have a range of cells (B6:M6) and I want to count how many have data. My problem is that all of the cells reference cells on another worksheet. Every cell starts out like =Bethlehem!B6. I want to ignore cells that have a reference in it when I count. I know how to do it if you want to ignore these cells as follows: =countif(b6:m6,"<> 0"). Those cell references return a 0 if they have no data in them. However, a user might actually enter a 0 in the referenced cell and I need to count that one. The above formula would exclude it.
View 4 Replies
View Related
Aug 10, 2009
I've got four columns of data. The first column contains pricing for a bunch of products from our company. The other three columns contain pricing for three other competitors. So for example:
Product | Our Price | Comp1 Price | Comp2 Price | Comp3 Price
Gloves | $4.59 | $5.00 |$6.00 | $3.56
Hats | $5.00 | | | $4.59
In column G right after Comp3 Price I have an IF statement that says if Comp1 Price is less than Comp2 Price, Comp3 Price, and Our Price, to print "Comp3" in that cell. If it is not less than the comparable data, then check Comp2 Price then Comp3 price in the same fashion. If Comp1, Comp2, or Comp3 is not less than Our Price then print "My Company Name." This will allow me to see who has the lowest price for that product and also tell me if that lowest price is lower than our price.
My problem is that I can't get it to work out so Excel ignores blank cells. So for example, Comp3 has the lowest price amongst our competitors for hats and is also lower than ours. But when I check for Comp3 price being lower than Comp1 and Comp2, it comes back as negative because Excel sees those cells as zeroes even though they're blank. My formula is:
=IF(AND(C2<D2,C2<E2,C2<B2),"COMP1",IF(AND(D2<E2,D2<C2,D2<B2),"COMP2",IF(AND(E2<C2,E2<D2,E2<B2),"COMP 3","MYCOMPANY")))
This would do exactly what I need Excel to do if it would ignore the blank cells. The problem is that Comp3 has pricing for hats because they offer hats, but comp1 and Comp2 don't offer hats at all, so naturally Comp3 has the lowest price; however, according to Excel Comp1 and Comp2 have the lowest price because the cells are blank and counted as zeroes.
So, does anyone know how to work this out so Comp3 will be counted as the lowest price for hats, ignoring the blank cells of Comp1 and Comp2?
View 10 Replies
View Related
Feb 5, 2010
I am trying to return the criteria MET, NOT MET & EXCEEDS. This works when each cell contains a number, but when one is blank the formula below returns "EXCEEDS" I'm guessing this is because is sees a blank as zero.
=IF(BN102<$BJ102,"NOT MET",IF(BN102>$BJ102,"EXCEEDS",IF(BN102=$BJ102,"MET")))
I've tried using this alternative but it still returns the same.
=IF(BO102<$BJ102,"NOT MET",IF(BO102>$BJ102,"EXCEEDS",IF(BO102=$BJ102,"MET",IF(ISBLANK(B102)," ",""))))
Basically I need to know how to make excel ignore the blank cell and not see it as zero thus returning "EXCEEDS". Although the cell values are integers they are derived from another cell using the following formula, I'm not sure if this makes any difference
=IF(ISERROR(VLOOKUP(BO11,$BH$63:$BI$87,2,FALSE)),"",(VLOOKUP(BO11,$BH$63:$BI$87,2,FALSE)))
View 3 Replies
View Related
Oct 18, 2009
I want to average the cells in column B but ignore values in the corresponding rows with a 1 or 7 in column C.
This will be used in a years data where 1 - 7 are days of the week and I want to separate weekdays from weekends when calculating an average.
View 9 Replies
View Related
Apr 19, 2009
I hope I could get more help this time, can someone help me out how to display the highest date together with the adjacent data on its row? I have attached a sample file.
View 7 Replies
View Related
Apr 22, 2012
Excel 2007 holds data of mine in a "format as table" Table. I want to rank the values in one column. Normally I want to do that by ranking the value in the current row against all the remaining values in that column. That is easy.
I got stuck today when I wanted to rank the value of the field in the current row with only a subset of the values in the column. I want to rank the current against the half-dozen or so records that have "A" in an adjacent column, and rank values having "B" in that other column against only the other values a "B" and so forth.
It would be best for all this to be done in formulas in the table rather than a clever use of a pivot or other? But really, I may be open to different approach than I was trying for if I can use successfully!
View 2 Replies
View Related
Aug 1, 2012
I have a sheet in an excel workbook which I export to a separate file and then save as a text document, I need to remove the tabs in this file, however the file (example attached) needs to be in a certain format to be imported into a piece of equipment which has a proprietary file format. Part of this format is the 2nd row and 5th row must remain present and empty.
[URL]
View 4 Replies
View Related
Aug 18, 2014
I have created a drop down cell based on the cells in the first row of a particular table. Now when i select a particular cell from the drop down i want to fetch all the values in the respective column of the selected cell on to a different group of cells. While fetching the data to those group of cells, i want to ignore all the null, blank cells. All this actions shall be automated and shall happen simultaneously on selecting the drop down.
View 1 Replies
View Related
Sep 5, 2009
Using formula rather than VB, I would like to calculate the number of blank cells that appear in a column BEFORE the first active cell but exclude any blank cells that appear after the first active cell. To elaborate, I have sheets that contain the days of each month and I need to exclude for other calculation purposes, the number of days (cells) where no entry of data has been input at the beginning of a month, NOT after the first data entry. ie. September has 30 days.
The first data entry is the 10th and there are no entries (thus far) after the 10th, the result that I seek, will be 20 (although only one cell has data), being the days left in the month AFTER the first entry. I have looked at COUNT functions but cannot find an solution. Perhaps it's not possible using basic formula?
View 4 Replies
View Related
Oct 24, 2011
I am trying to get a rolling 8wk avg of a large group of data. I am trying to take the avg of 8 vlookups:
=AVERAGE(VLOOKUP($A$9,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-7,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-14,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-21,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-28,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-35,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-42,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE),VLOOKUP($A$9-49,'Raw Data'!$A:$DV,MATCH(B8,'Raw Data'!$2:$2,0), FALSE))
However, in some cases, the cell to be looked-up may be blank. Using the formula above, the result of these vlookups is "0". I want to take the average of these vlookups excluding the blanks from the 'Raw Data' sheet.
View 6 Replies
View Related
Dec 28, 2007
I have a workbook which has data in column A. I also have data in column I. What im trying to do is copy and paste the data from column I to A but if the cell in column I is blank I need that ignored and the data thats in column A at pres kept.
View 9 Replies
View Related
