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 have a set of data which is the total monthly sales whereby each customer has several entries.
I have created an automated invoice through a vlookup system, yet i encounter the problem that i need to return each entry for the same customer into the automated invoice. But my vlookup will only return one entry for each customer as it is looking up the customer code and just brings back the first one it encounters, is there any way to bring back all entries for each client?
I have formulas in wksheet #1 going down 20 columns (they are all the same formulas). On wksheet #2, I need to understand how to switch all column references in only one column to another (from wksheet #1) to show different sums. Clear as mud? The way I want to do this is by having one cell on wksheet #2 where I change a number from 1 to 20. This in essence would be the column heading in wksheet #1.
Example: *There are more formulas but for the purpose here I'll use 3 for each Wksheet
In wksheet #1 I have C24 is =SUM(C14*C15,C16*C17,C18*C19,C20*C21,C22*C23)*12 C55 is =IF(B66=0,"",C7/B66) C56 is =IF(C11=0,"",C7/C11)
In wksheet #2 I have .......................................
Cell A1 is calculated by entering a number in cell E1[COLOR=blue ! important][COLOR=blue ! important][/COLOR][/COLOR]. Due to the various formulas used, when cell E1 goes up, the value in cell A1 goes down (and vice-versa - When E1 goes down, value in A1 goes up). Cell D1 is calculated using formulas UNASSOCIATED with A1 or E1.
I can't enter a formula for cell E1 to do the calculating due to the circular reference created.
I need some type of code that will automatically figure out what number needs to be in cell E1 to make cell A1 equal Cell D1 without creating the circular reference.
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
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.
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.
In A2 is the Stock price, and B2 the time of the update.Now every time it updates A2 and B2 get overwritten.I need some code that will save/ record the the stock price on the hour, every hour. I then need the highest and the lowest price within that hour, and lastly the price at the very end of that hour. (example)
I received what I think may work, but I dont know how to write this in VBA. If the time of the new value received > than B2 + 1 second, create new row where Open=High=Low=Close else if (last column=new value,if new value>high column then high column=new value,if new value<low coulmn then low column=new value.)
find the attached file there is a form which i have created to update a data sheet, there are some fields in the Sheet which are there is the form, but what i want is in the form once i enter the data in first 2 to 4 fields 7th and 8 th, 9th ,10 th fields should updated based on some formulas given for caluclations:
See attached file: i want the following fields to get updated automatically
1) Tripcode should get updated as CONCATENATE of Cab type and trip type ( See Sheet "ATT" for formula i have mentioned)
2) Trip cost is based on trip if trip code is TTA then some value or if tripcode is TTB some value like that...
formula i have used in the sheet is...:- IF(J3="TTA","750")+IF(J3="TTB","700")+IF(J3="TTC","650")+IF(J3="TSA","750")+IF(J3="TSB","700")+IF(J3="TSC","650")+IF(J3="TQA","750")+IF(J3="TQB","700")+IF(J3="TQC","650")
3) Extra Kms Cost is field Extra Kms * 5(Some Value)
I use daily an Excel workbook that comprises 6 sheets. The first sheet has some very clever vba which at the click of a button converts the data in it to an xml file. I have looked through this vba and have on occasions, modified it slightly, sometimes successfully, sometimes not!! This is what I am wanting to do...... At the moment my data is entered into a different sheet in the workbook. This is because there is also additional information on this sheet that is not required on sheet 1. When a row in this sheet is completed the part of the row that is required is copy and pasted to the next available row on sheet 1. I then go back to where it was copied from and fill that row in a different colour to show me that it has been 'processed'
Although Sheet 1 is capable of 500 rows (set in the vba), I always produce my xml file from it when I get 20 rows of data. So, what I would like to do is be able to click a button, or a key press to:- copy part of a row of sheet 2. paste that into the next available row in sheet 1 ( upto a max of 20 rows)change the complete row of the copied data in sheet 2 to a different colour (preferably 'fill')
I am working on multiple sheets that collate to an “averages” sheet. The Averages sheet would need to collate the information historically.
I need a code that can be assigned to a button so when it’s clicked, the figures for this week are added to the figures that were from last week. So for example.
Worksheet 10, Rows 12 – 20 have the data from the last 8 weeks.
Worksheet 09 has this weeks data and a button to push. When pushed it adds the data from this week to row 21 in Worksheet 10.
The following week the button would add the data to row 22 and so on.
Essentially each time the button is pressed it drops the information in a row one lower than the last time the button was pressed.
I want to record the amount of people attending a venue over a given period. The information presents names and did they attend. In column "D' from D5 to D45 the information will be yes or no. I want a formula that calculates the amount of YES and shows the answer in a numeric format
I am trying to collect data from a server. The data comes through as .csv (seperated data), and I am able to get all the useless info/columns out of the way - but I would like to keep a record of how many times these "alarms" come in. form a spreadsheet, or tell me how to go abouts using a tool to simplify my process.
i am preparing my self to enter the microsoft Excel 2003 Exam.....
one of the mentioned points in the preparation list is: (Perform data analysis using automated tools) how to use the automated tools? and if possiable to get a file that i can apply these tools on it
Im doing a project and Ive heard that previously, this was done by copying and pasting which took countless man hours to do.
I want to make automated reports of call center data per day per agent. I would like for it to exported into a single excel worksheet with a tab for each Agent, listing the stats per day. What program would be the best to accomplish this feat. I was thinking of importing the information to access to create to muniplulate the information in a one click fashion. but im not sure if i could get the data back to excel.
I use Office 2010. I have an Access database that goes out and runs various queries from our systems of record. It then takes the data and dumps it into an Excel file. I have taken the info and created a file the grabs the raw data, converts it into Pivots and then Charts. All info is automatically updated however the size will change depending on what department is quering the data. It is really all done with a click of a button. I have tried to merely uncheck the "(blank)" but it comes back everytime the report is ran.
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'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.
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
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".
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
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....
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?
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 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 am working on a project and would like to see if there is a solution for it. i have a workbook that has data entry that is summarized at a master level but I need to automatically use a formula to update another sheet after clicking an udpate button. The data from the data entry sheet needs to be allocated to all the lines that has the same master item based on the formula. A test workbook is attached ...
