I have a multi tabbed page and the page i want to sort (2) is looking up the 1st page and creating one column list from cell a1 to a280 and it includes a number of zero's from the look up pages because that is the correct result. I already have it sorting but I want it to sort and leave the zeros at the bottom (they are currently at the top) of the sheet so I can just print the info I need.
Can I create this in sort or do I need a macro and how can i write it as I am very green on VB.
i have a mock up football issue here i want to enter scores in sheet 1 and they automatically fill sheet 2 etc i know i can just =and copy sheet 1 A5 and so so but that leaves zeros which will start allmy formulas with 1 point i need a formula that will leave sheet 2 3 etc blank untill any score is entered
From time to time a have columns of numeric data that I want to sum on the last row. Occasionally, I like to sort Top to Bottom. I have always wondered how to stop the bottom row or total column from moving directly to to the top?
I need to create a leave tracker wherein i will get the names of the person who want to take leave in that particular month. I have prepared a calendar to make an entry. I need to take care of the following things
1) The person must have sufficient leave balance available to take the leave. so i have to first check if he/she has sufficient leave balance.
2) There are four team leaders and each team has around 13-14 members. so if lots of people from the same team apply for the leave then they will not be eligible for leave. this i want to know in percentage as to how much percentage of people are taking leave from that particular team.
What i want to do is clear some data within a merged cell but leave the rest.
I have added a sample workbook. The data i want to clear is highlighted in Red so the 11111 will be replaced with ...... and the 09/12/09 will also be replaced with .......
with the data in the attached sheet, I create several different pivot tables that need show the count of the information in the columns M:DU. My issue is that the data is sent to me from a third party and the columns contain zeros that cause the counts to inflate.
What I would like to be able to do is run a macro that will search out any zeros in M:DU and replace them with a blank cell.
Unfortunately the number of rows increases with every monthly reporting cycle so the macro would need to be able to accommodate for that.
I have used the snippet of code below many times to delete rows of filtered data. For example, if I want to delete all rows containing "Y" in column F, this code would filter to that criteria, offset it to save the column headings, resize it to avoid deleting the row below the range, then delete the rows. This works beautifully, until I ran into an instance where there were NO "Y"'s in column F. Then it deleted the column headings.
How would I modify the code below to leave the headings in row 1, but delete any other filtered data? (Also, if there's a better way to delete filtered rows but leave headings, I would be interested.)
I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this: =INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
I'm using a formula that I got from a previously made excel sheet. The formula does what I need it to do and looks like this: =INT((D10-10)/2)
The problem is, if I don't enter a number in the cell D10, all the cells with this formula show -5.
If I don't want data entered into D10 yet, I'd like all the cells with that formula to be blank until I actually enter a number in D10.
I think it has to do with using an IF statement followed with ""? Am I on the right track?
Also, if I have other formulas like =SUM(AP3:AQ6), but the cells it refers to are blank, how can I make the cell with the formula also be blank rather than show a 0?
I know I can turn off the "Show a zero in cells that have zero value" option, but I was wondering how to do it with the formula instead.
A1 has a date of 03/10/07 B2 has formula =A1 + 7 I want to copy that formula down the column B2 but if B3 is empty I get 01/07/1900 How Do I make so that if there's no date entered in A column I get blank cells but still retain formulas, I've been running into this issue with other formulas but I couldn't find any solution searching this forum,
I'm trying to create a worksheet to automatically make a football league table update once results are entered. I have entered a formula using the "IF" function which compares scores and then awards points. However before a game is played and the cells are left blank, the formula detects this as a "draw" and awards a point. how to prevent the formula operating until a result is entered?
I have data I extract out of a datasource. I run a few excel scripts upon it and export the content to another spreadsheet. The column of data (I have columns a - h) i'm concerned with is H. The totals in that column regardless of the length of the column (could be 10 rows of data or 10,000) I need to delete the top 10% and bottom 10% rows completely of those numbers in H and leave the remaining 80% in tact.
I have a 23 sheets in a workbook and in each sheet i have around 1200 to 2500 data.
What i want is : from active sheet i want to open another sheet which is located at my desktop and should select column A, H & I all data (whether it is blank at last not to worry) and paste it into my active sheet in column A,B & C then i also have formula to calculate saving per shoe in column D,E & F.
For column D,E & F in active sheet the formula should fill down until the column A gets blank (For example : in column A,B & C under active sheet takes 1230 products so formula of the column D,E & F will end at 1230 row)
I have tried to record macro for the same but this is only for the restricted rows i.e. no. of row so I post it now.
I have many rows of data 6 columns wide. I want to be able to enter data into a specific section, then run a macro to "cut-and-paste" that data onto the bottom of my existing data (with one empty spacer row between the new and existing data)
Here's what I have so far:
[Code] ....
Basically the part I need working on is changing [ Range("A101:F130").Value ] to be dynamic. For the code to determine the last row of data, move 2 rows down, and paste the block there.
I’ve created a formula for this statistic and I’m happy with the results. Because I’m working with formulas, my only problem is the unwanted zeros. How do I hide zeros that show up automatically (i.e. #3 [blank] and Nov 09-June 10)? I can hide the numbers, but if I enter a zero to one of my future statistics it will not appear and I don’t want that to happen. Is there a way to hide those automatic zeros without affecting my real zeros?
I have a leave history report with data for leave taken for the 2014 year. The sheet contains the following headings:
Name; Leave Start Date; Leave End Date; Date Paid; Days Taken; Roundup; Offset
The roundup column rounds the days taken up, for example if someone took 0.4 days off it would round it to 1.The offset column just subtracts the Roundup value by 1 (=Roundup-1) I think we will need this for what I am trying to do.
I have created a new spreadsheet labelled gaant chart, this shows the name of the employee with the working dates for the year and has the following data:
Name 1/01/2014 2/01/2014 3/01/2014 6/01/2014 7/01/2014 8/01/2014 9/01/2014 10/01/2014
[code].....
What I would like for the macro to do is lookup Employee 1 from my gaant chart table and search the leave history data if it finds a relevant start date for the employee it marks that date with a 1, it then looks in the offset column and marks the number of offsets to the right. For example if employee 2 took leave from 1/1/14 to 3/1/14 the macro would mark a 1 on the 1/1/14, and search the offset column which would have 2, it will then mark a 1 in the 2 cells to the right of 1/1/14 also (these being 2/1/14 and 3/1/14)
I have a column of data that comes from simple division formulas, E2/F2. I am wanting to take the Percentages given and quickly recognize the ones in the bottom quarter.
100% 75% 23% 39% 50% N/A 100% 5%
8 items - the lowest 2 (the bottom quarter) should "stand out" - 5% and 23%
I figured two options: 1.) Conditional Formatting - don't know what the formula would be to use 2.) If statement - preferred method.
I would like in the cell next to the percentages for there to be a blank cell if the amount is not in the bottom 25% and if it is I would like the cell to read "Yes" I thought something along the lines of =if(G2 in range(G2:G100)is bottom(.25),"Yes","") Of course that is not real code. I'm hoping someone will be able to edit that to make it functional. Or guide me in the right direction. And you may have noticed the "N/A" in the list above. I would like excel to view this as 100%, at the least just ignore it and don't give me an error.
The following code is doing what I want it to do in part, its copying the data in range k3 to q3 to the next empty row in columns a to f
It works when I change any cell in column K (11), I want to change to only on a change in K7 or K8? As an afterthought if I'm dealing with the same sheet do I need to make reference to sheet2?
VB: Private Sub Worksheet_Change(ByVal Target As Range) Dim nextRow As Long If Target.Column = 11 And Target.Cells.Count = 1 Then
Data consists of a series of ranges from columns A:N and rows varying in number between 3 and 30.
There are two blank lines between each range of data.
Each range is (with exception) numerically ordered down column H (e.g, 1, 2, 3, etc).
Problem: there are around 1100 occasions when a range contains a row of data which is to be disqualified from consideration. On these occasions the data always appears in the top row of the range and is identifiable in column H by the nine codes 111, 222, 333, 444, 555, 666, 777, 888, and 999).
I wish to find a macro which will :
1. where the top row of a range in column H is one of the codes 111 to 999, transfer that row of data to the bottom of the range.
2. delete the now empty top row from which the transfer was made from.
3. insert a new blank row at the bottom of the range below the row which the data was transferred to (in order to maintain the two blank rows above and below each range).
I am trying to add a macro that finds a dynamic range (number of rows changing with different data sets) sorts column B, then column A, moving blanks to the bottom. My dilemma is as follows:
I have a template with formulas that cannot be changed (I cannot paste special values, or clear the cells). Column A includes HLOOKUP formulas for cells A14:A120. Column B includes VLOOKUP formulas for cells B14:B120. The different data sets I pull in have different numbers of lines, which is why I need to maintain the formulas. However, I cannot seem to find a way to accurately sort column B first from smallest to largest and then column A from smallest to largest. In all data sets there will be at least a few blank lines that I need to move to the bottom. However, due to the formulas within the cells (column A returns a value of 0 and column B returns a value of “ “), excel does not recognize them as being blank. Here is a sample data set.
Branch S&C Group 77770000583-DOWNERS GROVE BRANCH MWBF2 77770000585-CHICAGO RIVER BRANCH MWBF1 77770000587-WEST TOWN BRANCH MWBF1
[code].....
In this case, I would like St. Charles & Algonquin to be at the bottom because column B is blank.
I'm looking for a simple piece of code I can use to manipulate the data on the attached workbook. Firstly, I need to be able to sort the data by column O ("Balance"). I then need the code to extract all the information for the top 10 and bottom 10 rows and paste the info into a new worksheet. The code will need to account for the fact the number of rows might increase/decrease with this report.
I have used a formula to achieve this before which seemed to work although now it seems to be replacing other data already in the worksheet so I am unsure of how to fix, thinking I should just start again.
The file in question is a tracker of activities, Column A (sheet1) is the Status and once the status is "Closed" I need to run a macro that copies all closed activities to Sheet5 at the bottom of previous closed activities. Once this is done I already have a macro to delete all "closed" activities from Sheet1 meaning that it is vital the new closed activities stack onto the bottom of previous closed activities.
The Column Headers start on Row 11 with activities running from Column 12.
