I have a list:
Item Code - Description - Quantity - Date
02099 - Blue Widgets - 500 - Jul 1
02099 - Blue Widgets - 200 - Jul 2
02099 - Blue Widgets - 300 - Jul 5
02099 - Blue Widgets - 500 - Jul 9
What formula can I use that returns the date (Jul 5) once the Widgets reaches 1,000 pcs?
I'm trying to return a distinct list of rows that filter based on the latest date and largest quantity for each distinct AccountID and ProductID combination.
I tried some variant of the =max function, but I need two filters.
The purpose of this is to create a data set of all company accounts with the most recent number of products used to upload to a database.
The simplified and original data set is as follows:
AccountID
ProductID
Company
Date
[Code].....
Cell I5 has the sum of cells A5:H5. Every time the value of cell I5 reaches the next million, I'd like for the date that it reaches the next million to be displayed in J5.
View 9 Replies View Relatedi am comparing 2 inventories, they contain the same information. I have included these on 1 sheet
Column A: Part numnber Inventory 1
Column B: Stock number Inventory 1
Column C: Quantity Inventory 1
Column E: Part numnber Inventory 2
Column F: Stock number Inventory 2
Column G: Quantity Inventory 2
I need a formula that says: If column B = column F, return the difference, if any, of Coulmn C - Column G to Column H
The data is not going to be inline, so B1 is not necessarily going to = F1
how to deduct a percentage off of a range of units.
For example, if I have 100 units that cost $50, I'd like to deduct 20% off of the first 10 units, and then have the total dollar amount put into a separate cell.
So, 100 x $50 = $5000
With 20% off of the first 10 units: $4900.
__________________________
20% of $50 = $10
$10 x 10 units = $100
$5000 - $100 = $4900
I have my tracking data sheet in my master file.
I wish to create a macro code to only retrieve the data on the file last modified date with the specific date.
For example like this :
I wish when I click on the List button, it will pull out the file that is from 28/5/2014 till 30/5/2014 which I have input in B6:B7
I have my code like this but it will pull out all the file in the folder:
Code:
Dim iRow
Sub list()
iRow = 11
Call ListMyFiles(Range("C4"), Range("C5"))
End Sub
Sub ListMyFiles(mySourcePath, IncludeSubfolders)
[Code] ........
I have two idential spreadsheets with several rows of items each with different packaging quantities, price break quantities and corrisponding prices next to each break quantity. I'm trying to build a discounting sheet on an idential page by using formulas that read off the price sell in the same relative position on sheet 2. Below is one row of sheet1 and the idential row of sheet2 - underneath the cells are the rules I would like incorporate:
Sheet 1
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6
Sheet 2
ABCDEFG
1Pack QtyQty 1Prc 1Qty 2Prc 2Qty 3Prc 3
210010.91000.744000.6...........................
Basically, it is a scheduling of approving or rejecting of request dates. whereby, i will receive the request dates from Google doc form and then i will extract the data from web into the excel. The data i receive will include the ID, Date request and Remarks.
With the Information in hand, the data will then be "plot" into a schedule (which is a year calender that i have created on excel) base on the ID requested from the web and the ID in the excel.
After plotting, i also want to check if the number requested date exceed the quota set PER DAY, if it exceed, then i have to reject, if its not then approve.
So here comes the problem.. After all the dates have reflected on the calender, I want to get the information from the calender result(which is whether the date is approve or reject) back on to respective ID and Dates requested.
I have attached a example excel for clearer view of my explanation.
I have a table that has a number of new starters and corresponding appointments offered, what I originally required was to lookup the chronological date after the new start date.
However this has now been scuppered by my boss who has requested that not only lookup the date, but also add who the appointment is with, but if I do this the first array formula stops working and to tell the trust I'm not to sure how to do it anyway.
I looking for a macro which will help to open a file with current week number in name.
The problem is week 1 is starting on 30/03/2008 (finacial year) and ends on 28/03/2009.
I've made two dimensional array (week number, weekday) with all the dates from that period.
I have problem with code to search through the array for given key, return index and write it into variables.
For now my code looks like:
Sub week()
Dim i As Long
Dim j As Long
Dim k As Long
Dim week(51, 6) As String
I have a very long Excel spreadsheet (>2500 rows by approx 20 columns) that consists of >400 customer statements. At the top of each invoice, in colun B, is the text "Service Requestor Contract Number", and 5 rows down is the respective contract number. Further down (and the number of rows differ for each contract as each has its own unique level of detail) , again in column B is the text "Current Month Charges", and on the same row, in columns K and O, the current month number of items sold and $ charges respectfully. The spreadsheet is NOT paginated for each respective statement.
What I need to do on a separate spreadsheet is sum, by contract, both the number of widgets sold, as well as the amounts due.
Using excel 2010
In cell U24 I have this formula:
=SUMPRODUCT(($E$3:$E$2000=51014)*($F$3:$F$2000=101)*($K$3:$K$2000))
Currently, as you can see, this is summing up the criteria specified from row 3 to row 2000...(which i made the last row 2000 becuase I will never exceed row 2000 with info)
What I am trying to do is in cell T22 and U22 be able to plug in date ranges and make the above formula give me the sums of thos date ranges.
For example in T22 put in 8-1-11 and in U22 put in 10-1-11, and have the sum of that date range populate in cell U24.
Now ther kicker is this: some dates are repeated so I may have six rows of date 8-3-11....and they will have to be part of the sum total.....and ALSO the dates are not in chrological order....I may have six rows of 8-3-11.......then four rows of 8-10-11.....then maybe another three rows of 8-3-11, and so on and so on...this is for all dates. Sorting the dates in order is not an option.
So, this works perfectly by itself: {= SUM(IF(MONTH(I4:I17)=1,G4:G17,0))}
And this works perfectly by itself: {=SUM(IF(YEAR(I4:I17)=2007,G4:G17,0))}
But this doesn't work at all: {=SUM(IF(AND(MONTH(I4:I17)=1,YEAR(I4:I17)=2007),G4:G17,0))}
SUM by both a specific month, and a specific year from a single date field?
if there is a formular that would say if a certain cell reaches a certain value it will add 1 to another cell for example
if cell B2 reaches 24 then 1 would be added to cell B3
way to do this in plain excel (no macros, I have sufficient knowledge to do it in VBA but certain circumstances do not allow me to).
I want to insert a relative formula into a cell based on a condition.
For instance, I have a number N=10 (changeable).
And the following cells with corresponding formula:
A1=1
A2=A1+1
A3=A2+1
........
Is there a way to automatically insert that relative formula to the next cell until its value is larger than N?
I don't want to use IF and then copy to 65000 rows either, it will create blank cells that I don't want to be there (and it affects the printout as well as the scroll on the right).
I am wanting to put a "1" in ROW 2, starting with COLUMN B until the sum reaches the value in cell A3.
Example: I have 10 in A3, I would like the macro to place a "1" in B2-K2.
example.png
I am sure this is simple and easy to do, but i am having difficulties achieving this.
I want the cell to fill as it gets closer to 100%, I am sure its conditional formatting but I cant seem to get it to work.
The problem that I have is rolling over the sum total after the meter read reaches thousand it starts at 1 again, but I need the total KMs traveled for example in the car dash board the odometer shows 945 when I started my trip and end at 24, how would I set up a formula in excel to calculate the total KMs traveled
View 9 Replies View RelatedWhen a task is completed I want the row wher it is in to be placed to another sheet(Archief). When in column F the value is changed to 'FIN' the row(A5 till K5 in the file I attached) has to be placed to the sheet 'Archief' below the last completed task.
View 3 Replies View RelatedI'm trying to come up with a way that will populate a table after each cell reaches a certain value.
My first column has rows with the total time required to perform a certain operation. The columns after that, represent operators that should be picking up a certain amount of tasks that do not exceed a set time.
Let's assume that the first row (total operation) has a total of 550 sec. The second column (1st operator) should assign a a value of no more than 200 sec. The third column (2nd operator) another 200 and the fourth column (3rd operator) another 150. So as to create a spill over effect.
The second row has a different operator with a different time. Let's say 300. These tasks should be picked up by a fourth operator with a value of 200 and then a fifth with 100. All in all I would like the table to populate as below.
xxxxxxxxxxxxxxxxxxxxxx operator1operator2operator3operator4operator5
operation 1550 xxxxxxxxxxxxx200 xxxxxx200 xxxxxx150
operation 2300 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx200 xxxxxxx100
ignore the "x". I used those to make sure that the rows aligned with the columns
the code below works fine except for the fact that it takes FOREVER to complete as it has to loop through almost 2000 rows... How to optimize this code so the macro stops as soon as it encounters an empty cell? There is currently only 50 entries in the database, but with time it will exceed 1000 entries and therefore i can't simply say Range("H2:H50").
Code:
If OptionButton1.Value = True Then
'Show all Car Cases
Sheets("User-defined Database").Activate
For Each cell In Range("H2:H2000")
[Code]....
I have the word department followed by the department code in column A in an excel spreadsheet ie Department 50. Then say after 30 lines it may say Department 51 and so on. I want to copy down the cell containing the text "Department 50" in coulmn A until it reaches the word "Department",I then want it to copy down that cell ie "Department 51" until it reaches the word department again and so on. How I can do this?
View 4 Replies View RelatedI have some information in a column where I have different types of references in each cell with some blank cells in between. I am trying to write a macro that will go down the column until it gets to a cell containing the reference "THIS PAYMENTS" and then copy that cell reference down until it reaches a cell containing "Y-T-D AMOUNTS".But I want the loop to carry on until it reaches another cell containing THIS PAYMENTS.I want this to repeat for about 3000 rows See here before and after result.
Before
After
PERIOD
PERIOD
[Code]....
I am trying to create a calculator which will show start date and end date based on the specific date provided.
EX: column A has January 1, 2013, column B has Wednesday, Column C should have a start date which supposed to be 4 days ago (December 28).
Let's say I have thousands of employees, but I need to determine who worked for me during a particular date range, and all I have to go on is their start date in one column and their end date in another column.
If:
A1 contains beginning date of employment
B1 contains ending date of employment
C1 contains specified beginning date (criteria)
D1 contains specified ending date (criteria)
If I have a worksheet and it has 100 rows, I would like for column D to auto-populate with a predetermined formula all the way down to row 100, not row 101.
View 3 Replies View RelatedI am having trouble setting up my cycle count spread sheet. For instance if my starting number is 1000 and when it reaches 0 or below it starts to count in minus when I just want it to automatically reset to 1000
View 3 Replies View Relatedi am trying to create a spreadsheet that will automatically plan a days work in manufacturing based on the items the factory is making, the different items obviously have different hourly production targets and id lke to create a spread sheet that knows how many we want to create what the targets are and how long it will take. i also want it to be able to know when it has reached the needed total and automatically plot the targets for the next product, so what i need is a way to change part of the formula when the column reaches a set culmative total
View 3 Replies View RelatedI have a table array which shows me that amount of each I have in each spot.
What I need is a warning to auto popup when it reaches a certain number.
I need this for many different cells.
So if cell m20 changes from 3 to 2 it should popup and say only two xx are available and keep going down from there.
I'm trying to match certain transactions between two bank accounts, where the only like values are going to be date and disbursement/deposit amount. Obviously vlookup will not work here because there are many duplicate values. In the attachment I put in a Vlookup in the Account 2 tab to try and demonstrate what I'm trying to do exactly, which is match deposits in Account 2 with Disbursements in Account 1.
After looking around I found that maybe a combination of INDEX/MATCH would work here but how to get it to work or even if it is appropriate in this situation.
