I am trying to automatically show the date of the last transaction per part number. I have attached an example. The formula should also consider the data in WH, BRN, and TRNTYP columns
View 4 RepliesI'm dealing with 2 spread sheets. The first, "Transactions", lists transactions by date, but they are grouped by series. So the 7 series dates are ascending but when you continue to the 8 series it starts at the first date of transaction and ascend to the latest. The second spread sheet, "Sheet1" is an inventory and lists date of last transaction, part #, Part name, sorted ascending by part #. I need a function that will reference the Part # in sheet1 and yeild the date of the last transaction made with that part #. I have not been able to figure it out.
View 24 Replies View RelatedI'm working through a large database on sheet 1 that has multiple transactions for each person, what i am trying to do is find the earliest date and the first amount that each person deposited money. The findings will go into a table on sheet 2
This is a dummy data of what i have: Sheet 1 - database....
I have an employee expense voucher with a column that calculates mileage. I also have a column that lists what the mileage rate is. I'd like for that column to adjust based on the date of the transaction/trip.
Here's an example. Trip between 7/1/08 - 12/31/08 = .585 mileage. Trip between 1/1/09 - 12/31/09 = .405 mileage. If someone turns in a receipt for a trip taken on 11/13/08, I want the form to adjust the mileage rate to .585. Wasn't sure if this is an indexing kind of thing, or if there's an if statement I could use.
I have it all set up already with macros and formulas and such. My next step is to record the sales data ( selected cells ) from the invoice to a seperate sheet I call Cashup. The idea being to record all relevant sales data for the day/trading session on one sheet so the manager can cash up the till run by 2-4 different people during the day. Its targeting a small family run business so security isn't currently the main concern although for future proofing I'm trying to build that in as far as possible. Long story short: What would be a good method for recording each successive sale's data from the same page each time the Finish button is hit , to a seperate sheet in a log format? Currently I have these problems:
1:The operation should record into the next open row on the Cashup sheet and not overwrite the previous record.
2:The record must ignore all formulas and record only the values in the cells . Currently a normal copy and paste results in " N/A" recorded in the cells in the Cashup screen.....( probably since all the data that needs recording is formula generated to begin with )
I currently have a database that is similar to the example I've pasted below:
TypeDateTimeA10/01/200910:00B10/01/200910:00C10/01/200910:00D10/01/200912:00E10/01/200912:00
As you can see the Date and Time are repeated for several rows. This is how the data I receive comes through as A,B and C refer to a single transaction and D & E refer to another transaction.
Ideally what I would like to acheive is:
TypeDateTimeTransactionA10/01/200910:001B10/01/200910:00C10/01/200910:00D10/01/200912:002E10/01/200912:00
Where Excel can compare the date and times of each row and look for matching rows above and below it and then fill in a column next to it indicating that x number of rows are linked to a single transaction - preferably labelling them in some order to I can tell how many transactions there are.
I'm working on a spreadsheet to track student test scores over the course of the school year. Each score set is grouped by student, but we need to be able to realphabetize when new students are added. is there some way to accomplish this?
View 3 Replies View RelatedExample:............
Where all question 1 fields are put into a row, 2 in another row, etc (this may be more than 2 sets of questions), grouped by the name field. I have had a look at Pivot tables but they dont seem to do what Im after.
I have a workbook (Sort Test.xlsx) where multiple rows belong to the same "group". Is there a way to sort by a value (e.g. name of the test, date of the test, etc.) while maintaining the formatting and keeping the "groups" together? There are 3 sheets in the work book. The first shows the sheet as is, the other two are examples of how I would like to be able to sort the data.
View 2 Replies View Relatedi have is Column A with groups of companys, each company takes up 3 rows, the second rows last 4 numbers are what i want the sort the groups on. as you can see there is no order to them at the moment.
so in theory if theres a company with the area code of 1000 their 3 rows would go to the top and company with aera code 1001 after that and so on.
I have a spread sheet in which some rows are grouped.
So I can see the + (or -) buttons on the side, and the 1 and 2 buttons on the top, to hide or show the separate grouped rows.
Now, depending on the value of a certain cell, I either want to show or hide the grouped rows.
So actually I want to control the +/- and 1/2 buttons indirectly.
Is this possible? Or should I do this by hiding rows instead of grouping them?
I have an excel file here that has a list of values. What I would like to do is write a formula that finds me the last transaction value of a particular item code. eg. the final list should show :
30100 cost 1.04 (June Value)
30103 cost 1.25 ( Apr- Value)
I am trying to identify each month the last transaction value of a particular item.
i want to create a function / macro for this scenario:
if ID123456 has the status open dated 01/05/07 and the user later decided to change the status of ID123456 to closed on 03/05/07. The graph should count the number of closed then lessen the count of open because the previously open ID is already closed.
How to group a list of cells with sum value shown.
Example,
A1 to A10 = Dates - D/M/Y (May)
A11 to A15 = Dates - D/M/Y (June)
and so on
B1 to B10 = Value for May transactions
B11 to B15 = Value for June transactions
and so on
What I wish to do is, group all May transaction a sum value of B1 to B10 so that it will appears this way (A1 = May ; B1 = Sumvalue). I also wish to have a way to expand and collapse. Something like clicking on a button or something to expand a list of transaction in the month of May. In this case, a list of transaction from A1 to B10 would appear. Clicking the button again will fold the list and will show (A1 - May and B1 - sumvalue)
Having n issue explaining a statement to a customer. I want to list his incoming payments and invoices in an Excel sheet. So going in order of the statement I'll list the in and outs but I would like a subtotal under each transaction so he can follow easier. Example
$68,920.77
Subtotal $68,920.77
$13,812.43
Subtotal $82,733.20
$12,563.14
Subtotal $95,296.34
$(20,000.00)
Subtotal $75,926.34
Is this possible using the subtotal command? Here is what I'm getting
$68,920.77
$68,920.77 Total $68,920.77
$13,812.43
$13,812.43 Total $13,812.43
$12,563.14
$12,563.14 Total $12,563.14
$(20,000.00)
$(20,000.00) Total $(20,000.00)
I have tried this. actully i am calculating aging for a particular transaction.
This is my needs. if A1 = between or equal to 0-2 B1 = 0-2 days
A1 = between or equal to 3-5 B1 = 3-5 days
A1 = between or equal to 5-10 B1 = 5 to 10 days
A1 = greaterthan 10 B1 = More than 10 days
Acrully am triying this formula =IF(A1<2, "0-2 Days","5-6 Days") if more than 2 scnario i cant...
I have a form that requests random info. All this information is saved to a worksheet. I would like to give each transaction a reference number. In order to do so I would like each time a new record is saved to add 1 to the previous reference number. Also I would like to start the transaction with a unique identifier "ie. rn1001".
View 4 Replies View RelatedSolution to keep track of bank transaction in a budget - both in and out
With:
=SUMPRODUCT(--(Table[[CAT]:[CAT]]=$C5);--(Month(Table[[DATE]:[DATE]])=G$3);--(Table[[IN]:[IN]]))
Table1:
DATE | CAT | IN | OUT
----------------------
The transaction CATegory and Date place the amount from table - column IN to the corresponding cell in my budget.
I would like my bank account to reflect both income an expenses from 2 columns in Table: IN and OUT
Workbook example: [URL] ...........
I have a spreadsheet and it shows a raw data dump of all sales transactions per product. This has been transferred via ODBC and I need some sort of query to filter out the data.
Is there any way I can write a query to only bring in the latest transaction based on the sale date per product?
Columns are:
Product Code|Last Sales Date|Cost Price
Here's my problem:
A) This is regarding a real estate transaction.
B) Assume the funds to acquire the investment total $60,000.
C) Assume the purchase date is 1/1/10.
D) Assume the sales price (including costs) would total $76,000.
E) Assume the sales closing date is 4/1/10.
F) What formula function would allow me to show the Annualized Rate of Return on this transaction?
I Know this is something I should have been able to figure out -- but when I use the XIRR function, I end up with a figure of 167% -- and common sense indicates that's just not correct....?
What I need to do is just find a way to show either the IRR or the Annualized Rate of Return for investment consideration purposes. Is it possible to do this based on one single transaction that occurs in a fraction of a year?
I have aprox 700 groups of cells "I selected a set of cells and named them". these groups cover 3500 rows. There sizes very in the number of rows per group but, all have the same number of columns.
What I'm tring to do is create a summary sheet. I want to copy all groups that have meet a single criteria to another sheet. The criteria will always be in the same column. The criteria is part of a function and will change each time I import new data into my excel sheet. Am I even close by trying to group them like this?
I have an Excel file with 70+ tabs and was wondering if there was some way to group them together, short of just making separate worksheets.
View 6 Replies View RelatedI have a few pages of information and have grouped rows collapsed to show only the summary information. When I try to print only visible summary rows, whether I use print, print selection, copy and paste to another sheet or copy and "paste special" values to another sheet, all the hidden/grouped rows print or the summary rows print separately on different pages. Is it possible to print only the summary rows.
View 3 Replies View RelatedI have a spreadsheet with one column for each day. I've grouped the days into months and now I would like to minimize (minimize each month) Is it possible to close all, without having to select each group?
View 1 Replies View RelatedHow can I check (using VBA) if 2 columns (F and G) are already grouped.
View 7 Replies View RelatedI have a bunch (between 5-10) textboxes grouped together into one object.
When I click one of them, I want its value to be written to a cell on the active worksheet. In other words, simply would like to read the value of one of the textboxes in the group. This sounds trivial, but at this point, I haven't been able to find the correct syntax to access this property. It seems that the fact that they are grouped hides access to their text? I am using Excel 2003.
I have managed to code a workaround, but its performance is atrocious (involves shaperanges, ungrouping, etc..)
I have a spreadsheet where columns belonging to the same topic are grouped together. For every group I want to calculate the sum of numbers in a row. How can I get all the columns belonging to a group?
View 8 Replies View RelatedI have daily data that has columns for day, day of week, week end, month, year. I am trying to create a pivot chart that displays the data as a line chart day by day and as a secondary axis column chart by week.
View 1 Replies View RelatedI have several years worth of monthly data that I would like to keep hidden so it is printer friendly but I also want to have a chart of all that data... Is this possible? Only idea I have is to basically recreate the data sheet but I don't really like that solution since it adds steps to the process and increases the file size...
View 2 Replies View RelatedI have sheets with over 40000 rows. I grouped them in 60 and I need the average for every group on the 61st (which is blank). For example, when collapsed I have visible the following blank cells:
A61
A122
A183
A244
and so on.
What I want is to enter a formula in A61 for the average of A1:A60 then drag down and have the correct values in all the rest (average of A62:A121 in A122, etc.).