# List Transaction With A Sum Value

Jul 20, 2009

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)

## Showing Latest Transaction On Product List

May 31, 2007

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

## Last Transaction Value

Jul 15, 2009

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.

## Date Of Last Transaction

Oct 1, 2009

I'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.

## Getting The Latest Transaction

Mar 5, 2007

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.

## Subtotal Under Each Transaction

Nov 21, 2006

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)

## Calculating Aging For A Particular Transaction?

Jan 5, 2013

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...

Oct 24, 2009

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".

## Keep Track Of Bank Transaction In Budget - In And Out

Mar 19, 2014

Solution 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] ...........

## Finding Earliest Date And Transaction By Name

Apr 2, 2012

I'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....

## Get Grouped Data Corresponding Last Transaction Date

Apr 26, 2008

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

## Automated Data Entry / Transaction Recording?

Apr 13, 2012

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 )

## Identifying The Same Transaction With Multiple Rows Of Data

Sep 21, 2009

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.

## IRR- XIRR - Annualized Rate Of Return On Single Transaction

Dec 2, 2009

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?

## If Statement/indexing: Column To Adjust Based On The Date Of The Transaction/trip

Jun 17, 2009

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.

## List 3 Different Values To List Of List Of 3 In Combobox?

Jan 12, 2014

how I can list 3 different values to a list of list of 3 in a combo box, is.

combo box

select high = 35
select middle 30
select low = 25

I have the names in the box I just need it to add a value (which I have listed 1 in each separate cells) to each selection

## Make Unique List From List Without Duplicates But Hold Cell And Pull Corresponding Dates

Apr 18, 2013

I have my dummy data, and I have (what I think) is how I want the data to be shown. My friend uses Google Sheets, but I prefer Excel. I am trying to convert the code because I am a stickler for excel. Typically I can convert codes some easily, but this is way beyond me.

For Column A: I want to create a list on sheet 'Setup!' based on ids!D2:D="yes". If that list has duplicate entries, I would like only the first entry to show up, but for the next entries I would like the cell to be blank. (this is important for the next step) For Column C: I want to have the corresponding dates go with the name entry. For Column D: I want to have the notes go with the corresponding date entry. (I believe I can manipulate Column C's code to do Column D myself).

I am also going to upload a data sheet, and an expected results sheet.

unique list.xlsx

## Excel 2010 :: Can't Delete Filtered / Visible List Rows From List Object

May 19, 2014

I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.

## Data Validation List Automatically Show The First Item In The List Rather Than Showing Blank

Jun 20, 2008

I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.

## Excel 2010 :: How To Reference Unit Price In List Two Based On Date In List One

Mar 8, 2012

I'm using Excel 2010 on a Windows 7 machine.

I have one sheet with a large list of dates and total prices.

I have a second sheet with a list of dates and unit prices.

I want to divide the total price in my first list by the unit price on my second list.

I want to somehow point Excel to the date in the first list, and then lookup the corresponding date in the second list to use the correct unit price.

My thoughts thus far have been along using VLOOKUP with WEEKNUM and YEAR but I've been unsuccessful.

## Programme To Automatically Select A Value From A List Or Range Once A User Has Selected Corresponding Value From A List Within A Combobox

Jan 28, 2007

i'm writing a refrigeration selection user interface, working from values on an excel spreadsheet. how to get the programme to automatically select a value from a list or range once a user has selected corresponding value from a list within a combobox. for example if a user sets the temperature of their refrigerator to -5 celsius i need the programme to automatically select the corresponding value of enthalpy for the air at that temperature.

## Named List: Data Validation To Restrict The User To Only Selecting Values In A List

Jun 15, 2007

I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.

Insert > Name > Create
Name: Fruit

Refers to:
banana,apple,orange

When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.

## Create List Of All Files In Folder And Make List Hyperlinks To File?

May 17, 2013

i have many excel workbooks in a folder i want a macro that will get the names of all the files and make the file names so extracted as a hyperlink to open the files.

## Extracting Summary List Of Larger List Showing Only Items That Have Quantities?

Apr 11, 2014

It is a product list of office stationary with codes, descriptions and quantities. Using this spreadsheet we input into the quantity how many we want of the item, all well and simple you could call the list a stationary order form.

What I would like to be able to do with this Giant list is to have a function that would extract items off the list into a cleaner consolidated form. As in, if I wanted to order some of item A, C and F, I would like for the second table to just Show ACF without every letter in between or blank cells.

I know a simple method would be to have an IF formula to return the information to a new sheet that I could simply remove blank cell rows (unordered items) and ready for print each time. But I would love to know if there is a better way this could be achieved.

I have basic/intermediate knowledge of excel and can setup the data if need be in order for the function to work, but I can't seem to find the correct function for this project.

## Syncing Two Lists Of Data For One List Of Individuals - One List Has Multiple Entries

Jan 20, 2014

I need to consolidate these two lists of data into one list.

First set is just a basic list of individuals with their data.

Second set is multiple entries for those same individuals. Each entry shows a subscription to a programme. The final single sheet should have one row per individual which shows all of the programmes they are subscribed to.

See attached, example sheet.

The real list is 3000 IDs, so need some kind of formula to do this.

## Autofill Vertical List Horizontally With 2 Blank Cells In-between Each List Item

Feb 14, 2014

I have a list in column L2:L352. I would like to be able to start at AD2 and drag horizontally so that AD2 = L2, AE2 = <BLANK>, AF2 = <BLANK>, AG2 = L3, AH2 = <BLANK>, AI2 = <BLANK>, AJ2 = L4, etc.... until L352 has been autofilled.

## Generate From Larger List Short List That Meets Multiple Criteria?

Mar 27, 2014

I have a large amount of data, 40,000+ rows about how students do in remedial classes including from whom they took the classes. I have pulled from it a subset of student success as relates to the separate instructors. Now I would like to extract a consolidated list of those instructors for whom the student gain on criterion based pre and post course assessments is above a specific level. I have no trouble writing if statements that will look at two types of data, the course taken and the average student increase to determine which instructors meet or surpass the criterion, but I can only do that within a list that includes all the instructors. Because there are more than an hundred such instructors, it would be easier for the dean if I could provide a short list of those that meet specific criteria. I believe that it would be an array that I should use, but I have been fooling around with this for 2 days now, and am finding myself at a series of deadends!

## List Displaying Data In Multiple Cells Based On List Selection

Jun 24, 2009

I am trying to create a drop down list that when selected will display data in the 10 rows below and 4 columns, so a total of 40 cells. I would like the data to be based on the selection in the drop down list.

The data looks like this,
RequirementsQtyResourcesQty
Level 1 Sentinel10Level 1 Sentinel105
Level 2 Sentinel10Level 2 Sentinel167
Level 3 Sentinel10Level 3 Sentinel156
Level 1 Banga20Level 1 Banga401
Level 2 Banga20Level 2 Banga307
Level 4 Banga20Level 4 Banga0

i have 27 different combinations of the above list with up to 10 different rows. I have a label for each one, but i cannot get the rows below my drop down list to display the proper values base don the selection in the drop down list.

## Delete Value Of Dependent List Automatically When Mother-list Gets Manually Deleted?

Feb 8, 2013

I'm currently working with 3 dropdown lists depending on each other using data validation and =indirect(). I arranged them like in a top-down approach where you have a mother-list and child-lists (categories-->subCategories-->subCategoryCriteria). The user should easily chose first a category then a subCategory etc... This part works perfectly

My question is: when a user chose in the dropdown list e.g. a category, a subCategory and a subCategoryCriteria and he now maually deletes the value in the highest level, which is the Category (mother-list), all sub level values like subCategory and subCategoryCriteria will stay. It shouldn't be that way, because it's not logical. If you turn a tree upside-down (top-down approach) and cut one of the higher positioned trunks, all lower positioned trunks will fall off as well, right? So, my question is: can I use a conditional formatting or a formula to delete or fade out the value in the lower lists when the higher list value is being deleted?

I tried to use If-formula in combination with indirect in data validation, like =IF(A1="";("");INDIRECT(B1))...just and example.

The value doesnt need to be deleted, it could also be automatically face out like white on white or something. It's just confusing for the user if he deletes the highest choice and the lower choices still stay.

## Creating A Dropdown List Using Matching Data From 2 Columns To Populate The List

Jun 11, 2013

I'm creating a spreadsheet to keep track of my costs of production in an online game. Within the game there are a range of spawned resources that appear for only a short time before being unobtainable these resources have specific types that is shared between multiple spawns of the resource but each resource spawn has a unique name.

My first worksheet lists all the resources and their various qualities and the later worksheets are meant to allow me to choose from a list resources matching the requirements of the item I'm looking to craft. The example i have shown in the second picture requires Tatooinian Fiberplast and Lokian Wild Wheat to craft so in the Chosen Resource column I would like to have a drop down list allowing me to select the named resource type i would like to use - for Tatooinian Fiberplast the only thing on the list should be Omnitwixi and for the Wild Wheat it should show Fizi and Krad

[URL]....

[URL]....

I am aware there are people with more pressing problems than computer games and as such