For example I have a data set that will grow over time say 13 rows, I want to write a formula that will only use the last 3 rows with data entered and get the Median of those rows. I know that the Median Formula for the entire 13 rows would be =MEDIAN(A3:A16). Anyone know a way to make a Median formula only look at the last 3 in any given time without manually updating the formula's range?
I have an excel 2003 sheet that collects data from Infopath forms. The forms are to record students who have broken school rules, when, where, repercussions etc. One column shows their class and there is a separate column for each rule broken.
I want to create another sheet to show each class down the rows and the columns to show each school rule. Therefore, each cell would show the number of each particular rule broken for each particular class. I have tried to do countif and sumproduct (if on sheet 1, column B the class is KA and on sheet 1, column M, the rule is bullying = how many times this has occurred).
I have data on 400 rows. Each row has a maximum of 10 cells with data, but many have empty cells with no data. I would like to sort each row to show values of cells in sequence and eliminate empty cells. I can use the sort row function but its a long process for 400 individual rows. Is there an easier way?
On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.
I need a macro that will create a sheet at the end of the workbook.
Sum data from a variable amount of sheets and display that data on the created sheet.
Here is a step by step:
Starting on sheet 5.
Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.
Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.
(Note: The data would also need started on row 4. Everything above row 4 is headers)
Here is a small example:
D E T U V 13019090W Part A1 68705500 Part B1 64202900 Part C-11 59634600 Part D1 26005300W Part E1
I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).
After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.
So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).
Users select a row number which then opens up a Userform. Comboboxes and Textboxes on this Userform are then populated with coloumn values (dependant on the row initially selected). If the row number was the same each time I could accomplish this by the following.
Private Sub TextBox1_Enter() TextBox1.Value = Range("a1") End Sub
but as its a variable I'm trying the following (which I think should work)
Private Sub TextBox1_Enter() TextBox1.Value = Range("a" & edi & "") End Sub
I think I need to declare the variable 'edi' as Public. If so where should this be? I've tried 'Genereal Declaration'
I have inherited a spreadsheet with over 800 rows (and daily expanding) and 14 columns of data. I've attached a sheet that looks similar, with only 200 rows for reference. Here are my questions:
1. How can I delete all rows that contain no values? 2. I want to fill an entire row of data yellow if the values in the final two cells (L&M) in the row are equal, and red if they aren't. How can I do that? 3. Column A contains only dates, from oldest to newest. I'd like an obvious visual clue for when the months change. Currently it's a long, merged, blue-filled cell that says "March 2011", for instance. It can't be color because all cells need to be filled based on certain criteria (see #2), and borders aren't obvious enough. 4. I want to click on the row number on the very right, but have it select only columns A-M, is that possible?
I'm also having trouble with autofill. It seems to only work on parts of the spreadsheet. I don't know what settings may have been changed, but I do have autocomplete turned on, and no clue what to do despite hours of googling.
Looking for a VB solution to parse each cell in column B if a specific word exists then hide the entire row. in this case the word or phrase will be (inactive) each row will be different words but can contain (
like this... ___A___B____________________________________________C 1 44 this row does not contain the word but has other 3 2 23 this row does not contain the word but has other 5 3 21 this row does contain the word (inactive).................Hide the Row 4 26 this row does contain the word (inactive).................Hide the Row 5 43 this row does not contain the word but has other 6 6 20 this row does not contain the word but has other 9
I have a report with sales statistics from today and yesterday. Then I have a summary sheet that shows the balance between the two. But I'm only interested in seeing the lines with a balance greater than zero. Is there a better way to do it than I've already done? I've attached an example...
I want to show values entered with percentage sign.(%) Right now, if i enter any value say,10 in a cell having percentage format, shows 1000%. For that, I don't want to enter 0.1. I just want to enter any value and see it with percentage symbol.
I have the following situation: I have some values in column A. other values in column B. I want to be able to display a list using the values in column A and values in column B. A1-> B1, etc.. Attach file: Book2.xlsx
I've tried creating a formual to only show the min values for each row,If any values in my table is equal to 0, than, it should not display a 0, but the lowest, positive number available. in my table. However, I'm not able to get it fully functionable.
I would like a formula to return the top 10 products sorted by value, but to return the colour name in order starting with the larges and the value in a different column. Ideally I would like to do as little formating as possible. I have attached a spreadsheet as an example
The attached sample is a simple version of the sheet that I'm using. The buttons are triggering preset auto-filters (try pressing the colored buttons, those work for sure :D). Is it possible to only show the number of rows that is input in the textbox - regardless of the actual row number, just count the first "x" visible rows and not show anything else. In O3 I tried to make a sample of the desired result. So ideally I will be able to press one of the colored buttons and then type in the number of rows that I want to see.
On a spreadsheet in excel from office xp, is it possible to display a row on the screen, but when you print the sheet it doesn't get printed? How do I do that?
I tried hiding the row, but the admin wants to be able to see it on screen and complained too tedious to unhide it on screen then hide it for printing.
I have never written macros for excel so not sure if there is a solution there, or if it can be done another way.
I also thought we could have the data from the row in another sheet that doesn't get printed, but she (the admin) preferred the hide/unhide solution.
I want to show/hide multiple rows based on the value of a cell on another sheet.
For example,
If Sheet1!A1="A" I want to show hide rows on sheet 2 as follows:
2 Show 3 Show 4 Hide 5 Hide 6 Hide 7 Hide
If Sheet1!A1="B" I want to show hide rows on sheet 2 as follows:
2 Hide 3 Hide 4 Show 5 Show 6 Hide 7 Hide
If Sheet1!A1="C" I want to show hide rows on sheet 2 as follows:
2 Hide 3 Hide 4 Hide 5 Hide 6 Show 7 Show
In my actual example the number of rows to show hide is greater (27 rows per selection) so a range, rather than specifying individual rows would be best.
in my document column "L" have some formulas,it's like this. =150*1.5+25*1.5 i need to make that formula to two part.it's like this. =150*1.5 =25*1.5 that two part need show below that formula cell in two rows.
I have a spreadsheet that has been set up with totals at row 1010. The data that the spreadsheet contains only goes up to row 159. However data will continue to be added row by row over time. At the moment I have to keep hiding and unhiding rows to check the totals. Is there a way so that the spreadsheet will automatically hide all but five rows between the last row with any data in and the totals at the bottom of the spreadsheet?
I have a worksheet with 4 sections of rows. Each section I call Goal 1 - Goal 4. I want to show each section and hide the other sections by clicking on a button. I have used a column, P, to denote which Goal a row belongs to by entering 1, 2, 3 or 4 accordingly. This is the code I am using.
Private Sub ToggleButton1_Click() With ToggleButton1 .Caption = "Goal 1" End With Dim rCell As Range If ToggleButton1.Value = True Then For Each rCell In Range("P2:P99") rCell.EntireRow.Hidden = rCell > 1 Next rCell 3) Else Range("P2:P99").EntireRow.Hidden = False End If End Sub
I have three questions -
1) This code works to show Goal 1 and by changing the >1 value to <4 I can make it work for Goal 4 - but I can't work out how to show the other Goals, 2 and 3.
2) Is this code an efficient way of doing what I want?
3) How can I make the "up/down" state of the toggle button actually relate to whether or not I am showing a particular goal? That is, if I click Goal 1 and then click Goal 4, I am showing Goal 4 but both buttons stay in the "down" state. I want the Goal 1 button to automatically return to it's "up" state when I click on another button.
I am creating a line graph to display changes in Cost Per Unit (CPU) of a given item over time.
The issue I have is that the CPU data on the line graph is displayed as 0 values where data is yet to be inputted. Instead I want the graph to remain blank where no data has yet been added. How can this be done?
find attached a simplified mock up of the spreadsheet.
Constant (fixed) values are: - Date - Hourly Rate Values inputted on a daily basis are: - Hours Worked - Volume Picked Calculated values are: - Total Cost - Cost Per Unit - Mean [=average(CPU)] - UWL [=mean+(2*stdev(CPU))] - UAL [=mean+(3*stdev(CPU))]
I'm open to using VBA if necessary, however as I will not be the only user of this workbook when it is complete I would prefer to keep it a simple as possible.
I am modifying a template from MS's site. Its a recipe tracker.
They have one sheet with a table on it. Some of the data in this table is pulled, using a formula, from the different worksheets (recipes). The particular formula they use is this:
However, when I try to do my own version of it, simply changing the '!Category' portion, it doesnt work. The cell just shows the formula and I cant figure out why. Sometimes, Ill type out the same formula that they have working and it doesnt work, which makes no sense to me.
Here is the template for reference: [URL] .........
I am creating a spreadsheet that allocates hours to different categories of work - for example, "Fire Protection". I need the allocated hours for all of the different Fire Protection duties to only show up when a value greater than zero is entered in the column at the top of this category entitled "Fire Protection". If "Fire Protection" has a value of zero, then all fire protection-related duties will automatically be zero as well - in effect, this category of hours is nullified.
I have done a spreadsheet that shows leave for staff. At anyone time there can be only 3 managers and 4 staff off as a maximum and the cell value for the day is entered as "AL". Therefore on a given day if someone tried to enter a fifth "AL" for example in the staff section a pop up displays stating they have reached a maximum.
