If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Moving information to another sheet with multip-le criteria
Thank you in advance for any assistance you can provide. I am still
trying to learn excel and am struggling. I have an order form for supplies that is updated monthly for various facilities. I want to store the information from each order onto separate sheets, according to facility and month. The order form is set up like this: Facilty Name Date of Order Product ID Product Name Quanity Wanted Unit Price Total I want the Quantity Wanted, Unit Price, and Total transferred to a worksheet for the corresponding facility. Each facility sheet has a list of the products. I was planning on having the months across the top row. I have spent many hours trying to do this using conditional information about facility and date and then using lookup tables to get the information regarding quanity and price. It worked for January - then as soon as I change the date, the information in January disappeared. I hope this makes sense. Any help would be appreciated. I also tried writing code for it using a command button to update the month. I didn't have much luck with that. Thank you in advance for any help. Carolyn |
#2
|
|||
|
|||
Moving information to another sheet with multip-le criteria
Why not make a single worksheet as a database of all your orders. Then you
can easily make your individual report using a pivot table under the data menu. when you say form, is this a spread sheet where the user enters data in the cells? If so, you would probably need a macro to move a copy of the data to your master database. Using formulas isn't really practical - as you state, the formulas recalculate each time the data changes. -- Regards, Tom Ogilvy "Carolyn" wrote in message m... Thank you in advance for any assistance you can provide. I am still trying to learn excel and am struggling. I have an order form for supplies that is updated monthly for various facilities. I want to store the information from each order onto separate sheets, according to facility and month. The order form is set up like this: Facilty Name Date of Order Product ID Product Name Quanity Wanted Unit Price Total I want the Quantity Wanted, Unit Price, and Total transferred to a worksheet for the corresponding facility. Each facility sheet has a list of the products. I was planning on having the months across the top row. I have spent many hours trying to do this using conditional information about facility and date and then using lookup tables to get the information regarding quanity and price. It worked for January - then as soon as I change the date, the information in January disappeared. I hope this makes sense. Any help would be appreciated. I also tried writing code for it using a command button to update the month. I didn't have much luck with that. Thank you in advance for any help. Carolyn |
#3
|
|||
|
|||
Moving information to another sheet with multip-le criteria
I would also create database in seperate sheet.
The secret is to use a macro to create a new row in the database that is below the top "live" row and then copy the linked data (which would be in the top row of database) to the newly created row using copy/paste/values. Thus, once the data is copied it is no longer linked to the form. In the very basic attachment, once the data has been copied, the form is cleared, ready for the next entry I have also added a column in the database that identifies the month of order. Have also put "autofilter" on the database so you can select any entry type in any column to show those entries only (click the arrow in the column title to see entries you can select from). The subtotal formula in H2 will add only the values displayed. So you can very easily select a facility and a month and any other criteria and see the total of orders placed. Hope it helps Nick Attachment filename: form 2 database.xls Download attachment: http://www.excelforum.com/attachment.php?postid=580301 --- Message posted from http://www.ExcelForum.com/ |
#4
|
|||
|
|||
Moving information to another sheet with multip-le criteria
By the way - you will have to save attachment first to get macro to
work. OK --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|