Print Data Based On Criteria
Dec 12, 2006
I have a sales workbook, with three sheets. Ongoing, Won, Lost, all potential sales are entered onto the Ongoing sheet and Ongoing is chosen in a column that has 3 options - Won, Lost, Ongoing, until brought to closure by winning the sale or losing it at which point Ongoing is changed to Won or Lost.
The products for sale are Audio, Video and System and at the top of the Ongoing sheet it totals those three categories as Won, Lost or Ongoing.
What I want the workbook to do is when the status of the sale is changed from Ongoing to either Won or Lost, the entire row is moved off the Ongoing sheet and placed on the appropriate Won or Lost sheet, and the totals at the top of the Ongoing sheet calculated accordingly.
One of the columns on the Ongoing sheet is 'Progress' in which a short abbreviated explanation is input of next steps of the sale. When the sale is closed, and hopefully automaticall moved to it's appropriate sheet, I'd like this text to be changed to either Won or Lost.
View 4 Replies
ADVERTISEMENT
Oct 23, 2006
I have a monthly template that has 31 tabs labeled 1 through 31 for the days of the month. I would like to add a macro that would print only the sheets that have a value greater than 0 in either of two specific cells.
Example:
Sheet / Cell B2 / Cell B36 / Action
1 / 0.00 / 0.00 / Don't print
2 / 100.00 / 0.00 / Print
3 / 0.00 / 100.00 / Print
4 / 100.00 / 100.00 / Print
I could add a summary sheet that would pull over these values and/or add if statements that return results like "don't print" or "print" if it would be easier in the macro or I could just use the code in macro to determine to print or not.
I am a record and tweak macro user and this isn't something you can record. I found some simple code in another post that would print one page based upon single criteria but it isn't exactly what I was looking for.
View 9 Replies
View Related
Nov 10, 2006
I have a report which is split into page sized chunks, in column P I have an identifier Planned, Booked or Running.
Is it possible to build a macro which says something along these lines
If P1 = "RUNNING" Then
Print that Page
Else
Go to P44
If P44 = "Running" Then
Print that page
Else
Go to P87
and so on until the cell selected is blank then stop.
View 5 Replies
View Related
Sep 5, 2007
I am trying to find a way to select and print multiple sheets based on a criteria or a list. I have a large worksheet with many sheets. Each sheet falls into one of three categories, and I want to be able to automatically print all tabs in each category. I have all of the sheets rolled up into a summary where I have access to all of the sheet name and print criteria.
View 4 Replies
View Related
Apr 16, 2008
I have been building a HR database. There are more or less 20 worksheets in it, including staff personal datas, contracts informations, overtime, leave etc.
All the calculations work, and I can print my payroll correctly. So everything is good.
However, what I really would like to do is to be able to print all salary slips in just one clic or two.
I think I have to create a VBA Macro to do so. But I never learned to use this tool. And I think this macro is too complicated to create for a newbie like me. So, I'd really like you guys to help me in creating it.
What I want this macro to do:
1° To check if the staff in question is still hired by the ngo
(I have on worksheet 'employee register' a column A named 'staff code' and a column Q named 'inactive'; i put a "x" when an employee is inactive, and the column automatically remains blank in the other worksheets)
2° If the staff is still hired, to print the salary slip of this staff
(I have a worksheet 'Salary slip' in which I have a cell (D5) that allows me to select the 'staffcode' in a validated list, when I select the staffcode, the information concerning the slip update automatically)
(my "staffcode" validated list still contains the inactive staffs, I dont know how to fix this)
3° Repeat this operation for all hired staff (all active staffcode)
View 5 Replies
View Related
Dec 15, 2008
got a great bit of code the other day from this forum to automatically print 'x' amount of copies based on the value in cell (see below)
View 2 Replies
View Related
Feb 8, 2014
I have a spreadsheet that is updated weekly -- but every week new info is added that needs a user to input corresponding info. I use a vlookup function to link to another spreadsheet that populates the info from previous weeks and the info that is missing shows up as #N/A...
First I was using a msgbox function to get the info:
HTML Code:Â
For Each b In myrange
If Application.IsNA(b.Value) Then
Employee = b.Offset(0, -2).Value
SSID = InputBox("Please enter ID# for " & Employee & " :", "New Employee Found")
b.Value = SSID
End If
Next b
But it can be up to 30 different new employees... and that is time consuming.
I would like to make it more user friendly by creating ONE userform that displays all of the employees as labels -- has a text box in which to put the ID # -- and then has a drop down box to choose the type of employee (2 options). I want all of that info to go back to the reference spreadsheet so it will be saved for following weeks, and then redo the vlookup to get the info into the new weekly spreadsheet (I can do that part)....
HTML Code:Â
Private Sub CloseButton_Click()
Unload UserForm1
End Sub
Private Sub ComboBox1_Change()
[Code] ......
View 2 Replies
View Related
Apr 15, 2009
I am trying to come up with a way to print out data that is variable in the amount of rows to print.
1. Cells C1 thru M6 is heading of report
2. Cells C7 thru M400 all have formulas and display information only when criteria in Column C in each row is met, if condition is not met it displays no informtion. (There lies my problem).
I need a way to print out only the area that information is displayed in and skip the areas that are not displayed. (Currently I have hard coded the print range using the largest report)
View 6 Replies
View Related
Aug 31, 2006
Hi, have a question regarding dynamic charts, specifically I am trying to create several dymanic charts based on data in a single column (So i can only say my first set of data will be starting a a set location in the chart, the rest has to shift down based chart data above it). This data should organized in charts depending on the data in several other columns. For instance, need to pull out values from column based some ID and some PartNumber. A sample excel or VBA would be much appreciated. I know VBA but do not know Excel VBA much.
View 9 Replies
View Related
Jun 28, 2010
I have a data sheet with 20 columns and about 300+ rows. In the results sheet there are 3 criterias (from column A, B, H in datasheet) which are in the form of dropdown in cell A1, A2, A3.
I would like to extract the data from the data sheet based on the criterias selected from dropdown list, to results sheet starting from row 6, with only 12 headers out of 20 (non-continuous, e.g. columns A-C, E, G-K etc.)
View 3 Replies
View Related
Dec 19, 2012
generating a formula that takes into account a range of values (an entire row) and from this row, I would like the formula to select, for example anything greater than 80%. After the formula selects anything greater than 8, I would like for it to select cells that are above or below the cells that have values greater than 8.
1
2 JLKNSTTP
3 85934942
4
5
For example, in the above datas, I would like a formula to select anything greater than 8 in row three and select cells above it. In this example it would be j, k, and t.
=if((3:3>8),offset(A3,-1,1,1))
View 1 Replies
View Related
Jul 16, 2014
I need to populate data based on criteria.
The criteria has been attached as follows.
Sample Test.xlsx
View 2 Replies
View Related
Feb 16, 2009
I need a data validation to prevent entries when they are > 50, but only if the value of another cell in col A is "Payment". The value of 50 is an example, I have another formula to get this number using VLookup.
The idea is that if the value of the cell in col A is "Income", I don't want the entry to be rejected. But if the value in col A is "Payment" or "Transfer" and > 50, I want the entry to be rejected (if I only have $50, then the payment cannot be > 50).
View 7 Replies
View Related
Feb 18, 2012
I have a problem and I hope that it can be solved using a macro. Basically, I have a set of data in column A and B in sheet(1):
A
B
1NameDate
2JOY12/09/11
3MAR12/12/11
4DAVID12/12/11
5
I have another set of data in Sheet(2):
A
B
C
D
E
1NameDatesalary
CHARACTERRATINGS
[code].....
basically, I would take into consideration the names and dates in sheet(1) and if it matches sheet(2) then it would then paste their salaries, characters and ratings into sheet(1).
View 9 Replies
View Related
Aug 17, 2012
I have a data which i need to total based on document types.
Document types in cells are AB=CELL A1, ZR=CELLL A2, ZP=CELL A3,
SK= CELL A4.And amounts in rows 555=CELL A1, 625 = CELL A2,
725= CELL A3, 925=CELL A4 and so on.
My challenge is to get all the total of ZR,SK,AB type of documents using macro.Initially i used this formula, however now document types are more than one.
=SUMIF([Book1]Sheet1!$C$2:$D$4,"zr",[Book1]Sheet1!$D$2:$D$4)
View 6 Replies
View Related
Nov 14, 2012
i have a sheet that i have been creating to pull information based on two things. The criteria is an emplyee # and the second is a date.
The data needs to be organized onto Sheet4. The Employee # will already be on Sheet4, so i have been trying to use that with Vlookup / hlookup with no success as i can't seem to get it to find the correct Date and place the data correctly.
Sheet4 layout:
Completed (with respective data from Sheet2)
A
B
C
D
E
1
[code]....
the data is located in Sheet2 (Emplyoee # is always in Column A, and the Date is always in Column B).
the data needs to be pulled into Sheet4. Essentially making the horizontal data of Sheet2, Vertical on Sheet4 under the respective date column.
Sheet2 Layout:
A
B
C
D
E
F
G
H
I
J
[code].....
View 3 Replies
View Related
Aug 29, 2013
I have data in my worksheet as follows
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
[Code]....
I want to create a sum of all values in column B (Data Size) that correspond to the same Dept-div code in column A
Ex: 0667 the total should equal 43268.8 for the two cells.
I need to count all cells in these columns versus a specific range?
View 2 Replies
View Related
Nov 30, 2006
I have a raw database that contains various data all in one column and I only want to extract certain data to a new column. All I want is to extract the time, like 14:00, 14:15, 14:30, etc...... from column "A" and populate column "D" if it meets this criteria.
I have tried everything I can think of, very difficult yet it seems to be so simple.
The "date" is really a date format but the "time" is really text.
Here is what the data looks like:
Column A has....
Name1:
Name2:
Name3:
10/16/2006
14:00
14:15
14:30
14:45
15:00
10/27/2006
16:30
16:45
17:00
View 9 Replies
View Related
Aug 11, 2008
I have a spreadsheet that has Leads in column H for eg Advertisements and Presentation dates in column K
I need to set up a formula that will count the number of dates (Items) in column K that is applicable to the item in column H for eg Advertisements, Referrals etc . There can also be blank items in column K which can be ignored
View 9 Replies
View Related
Aug 27, 2006
I've looked at the various examples in a search of topics simillar to my topic but I still can not get thsi to work.
I have a work book that contains 2 sheets
Sheet1 is called RawData of Multiple columns
Sheet2 is a quick report basded on Sheet1 data.
I need to find a way to do counts where 2 criteria are met.
Example.
Count the number of Times the phrase Major Victory appears in column M
( Range M1 to M50 ) of Sheet1 IF the value in Column L ( Range L1 to L50 ) of Sheet1 = USA
I tried this:
=COUNTIF(RawData!M1:M50,B1( AND(RawData!L1:L50=$A1)))
I am using B1 and A1 ( from Sheet2 ) as search criteria
but it gave me 0 ( and I know this is wrong ).
Based on the reading I did on simillar topics it appears that I cannot use CountIF in this case.
View 9 Replies
View Related
Jul 26, 2013
I am trying to create a sheet which will automatically calculate data based on laid down criteria.The criteria are as follows:
Cab Type in cell () Indica
If the value of cell () is less than or equal to 40Km then cell (B3) will be 400.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 8 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B3) will be 800
For every extra km above 80 the cell value in cell () will be extra km done multiplied by 12 in cell (D2)
Cab Type in cell () Indego
If the value of cell () is less than or equal to 40Km then cell (B3) will be 600.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 14 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B2) will be 1200
For every extra km above 80 the cell value in cell (B2) will be extra km done multiplied by 16 in cell (D2)
Ther are about 5 different types of cabs in the sheet.
Cab Type Provided
Total KM run
Basic Amount
Extra km
What I want to achieve is as under:
1. If value of cell A1 = Indica and value of cell B2 is entered as 40
Then the values of cells (C2:e2) should populate automatically as 400, 0, 400
2. If value of cell A3 = Indica and value of cell B3 is entered as 40
Then the values of cells (C3:e3) should populate automatically as 400, 40, 440
View 8 Replies
View Related
Oct 22, 2013
Attached is my .xls with dummy data Fetch data By Director.xlsx.
My intention is that once the Director and Month in tab 'By Director & Month' is chosen and the Fetch Data commandbutton is clicked, a new workbook should open.
This new work book should have :
all entries in tab 'Monthly Invoice Input' for that Director-Month combination,along with a sum total of column Q(Total invoice amount) in the last row.
View 6 Replies
View Related
Jan 24, 2014
I have a workbook with several different worksheets which I am copying from the individual sheets and pasting into a summary sheet, and multiplying the numeric numbers by minus 1 to reverse the signs.
This is all working fine, but I now want to copy an additional worksheet called "Plan" and paste this into the same summary worksheet, however I do not want to reverse the signs for this data.
All of the other worksheets begin with "Total" in the worksheet name, and this is how I am specifying which sheets to copy. I can re-name the "Plan" sheet to "Total Plan" if that makes the logic easier, the import thing is that the signs are not reverse for this sheet.
Column Z of the summary worksheet "RDBMergeSheet" identifies which sheet the data was originally copied from, so could I somehow use this to say if column Z = Plan, skip the multiplication by minus 1?
My current code is this:
VB:
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
[Code]......
View 3 Replies
View Related
Apr 5, 2014
show/display data based on month criteria, with selected month in cell H2 (as start date) and I2 (as end date) my expected result start from cell H7, i called "blue area"..
see my attached workbook..
View 9 Replies
View Related
Mar 30, 2009
I need to remove any rows containing "EEE" in the size column.
I also need to remove any discontinued ("D" in the "Type" column)styles that are currently not in inventory. For example 05-5490 does not have any available in either width "D" or "EW" and should be removed; however, 28-112 does have stock and should remain in the spreadsheet. Anything with an "F" in the "Type" column should be left alone.
View 13 Replies
View Related
Jan 6, 2010
I have a worksheet with several columns of data that I have to summarize elsewhere on the sheet. Here are the columns:
ID
Status
Joined
Name
Name can take on 4 possible values. For each value in Name, I want to count the number of cells in a column that fit certain criteria. Here are the counts I want to make:
Status OK: Status is a date.
Joined OK: Status is a date and Joined is a "1".
There are going to be about 500 records to count. I've already implemented these counts in a Windows scripting language that interfaces with Excel, but I know there must be a more direct and faster way to do it entirely in Excel, whether with VBA or writing functions into cells.
View 3 Replies
View Related
Mar 17, 2014
editing a datafile. I have a spreadsheet (Sheet2) that looks like this (Start Date is entered in J2 in Sheet 1 and End Date in J3 in Sheet1):
Date & TimePatient IDResultLevel
13/02/2014 14:1818.5>=4 AND <=15
13/02/2014 14:1027.8>=4 AND <=15
13/02/2014 02:231122.5>20
13/02/2014 06:191116>15 AND <=20
13/02/2014 10:081110.2>=4 AND <=15
13/02/2014 14:111113.4>=4 AND <=15
13/02/2014 17:541112.9>=4 AND <=15
[code]....
I need to find the maximum and minimum value for each subject. If the min>4 and max<10 and the subject does not have a value on either the last date (J3 in Sheet 1) or the date before that (J2 in Sheet 1 - 1), I need all of their data removed from the spreadsheet.
View 1 Replies
View Related
Nov 8, 2007
I am trying to sum data based on multiple criteria..
The english version of the formula is Sum all refunds for Store during week
Original Data Format: ....
View 14 Replies
View Related
Aug 22, 2008
I have a bit of a challenge. You'll have to take a look at the attached files to understand what I am trying to do. I am asking for your advice. I am creating multiple spreadsheets (QPPcompany.xls) that will refer back to a master data(QPPdata.xls) file.
I am trying to figure out how to best set up the data file so that I can do lookups on the data from the referencing files. Here's the challenge. I need to be able to find in the data page the company name in Column A and then how many L's (or C's or P's) they had in a specified month. So for instance I need to be able to find the number 3 in cell E6 and place it in B15 of the referencing file. The required criteria for lookup are Company Name, Month and then L, C or P.
So my question is, is this possible with how my data file is currently set up? I think it may be a problem to have the month/year in row 4 refer to 3 different colums for each month (L, C and P). I don't mind making changes to the data file to make these lookups possible and I think it may be necessary.
View 14 Replies
View Related
Jan 10, 2012
Error in the code, where I'm trying to copy the data (based on criteria) from one worksheet to another, then delete the data from the first worksheet. In the code (I have complied from this message board) I get an 1004 error "Application-defined or Object-defined error".
Code:
Option Explicit
Sub CopyALColKYes()
Dim NR As Long, c As Range, firstaddress As String
Application.ScreenUpdating = False
NR = Sheets("Completed-Expired").Cells(Rows.Count, 13).End(xlUp).Row + 1
[Code] .........
View 9 Replies
View Related
