Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel - automatic information from one sheet to another??
What I need help with is the following:
I would like to have it so, that when I fill in information on one row in the main document (sheet 1), that the whole row (in sheet 1) automatically will be copy/pasted in the correct underlaying sheet per category (I have different sheets for different categories). In the main document (sheet 1) the information is divided into different product types in each row - however there are about 10 different product types / categories. When "product 1" is put into the main sheet it would be nice if this automatically is shown under the sheet "summary for product 1". Doing so enables me to have a correct sub-total per category which can eventually be linked to the "total-sheet". Any suggestions on how to do this correctly? Hope you understand my question :-) Kathrine |
#2
|
|||
|
|||
Hi Kathrine
Rather than have all of the separate sheets for each category, you could just enter data on Sheet 1 and use a Pivot Table to show the results for each catgeory. If you nee some help on setting up Pivot Tables take a look at http://peltiertech.com/Excel/Pivots/pivotstart.htm -- Regards Roger Govier "Kathrine J Wathne" wrote in message ... What I need help with is the following: I would like to have it so, that when I fill in information on one row in the main document (sheet 1), that the whole row (in sheet 1) automatically will be copy/pasted in the correct underlaying sheet per category (I have different sheets for different categories). In the main document (sheet 1) the information is divided into different product types in each row - however there are about 10 different product types / categories. When "product 1" is put into the main sheet it would be nice if this automatically is shown under the sheet "summary for product 1". Doing so enables me to have a correct sub-total per category which can eventually be linked to the "total-sheet". Any suggestions on how to do this correctly? Hope you understand my question :-) Kathrine |
#3
|
|||
|
|||
Hi
Instead to have ~10 additional sheets, why not to limit it with one report sheet, where you can select product type from drop-down, and the table on report sheet is populated with wanted information? To get summary info, you can use SUMIF(), COUNTIF(), and SUMPRODUCT() functions. To get detailed info (rows from main sheet) about product category, add a hidden column as leftmost into table on main sheet, where for every row with same product category as selected on report sheet, the order number is calculated - leaving non-matching rows blank. P.e. for cell A2 =IF(B2=Report!$B$1,COUNTIF($B$2:$B2,Report!$B$1)," ") Now you can use LOOKUP() to retrieve information from 1st, or 2nd, etc. matching rows from main sheet into report sheet. Another possible solution is to have in main sheet header formulas, where summary data is calculated using SUBTOTALS() function - depending on parameter, it calculates various summary values from filtered data. Now set autofilter for main table on, set filter to desired category - and here you have only rows for this category displayed, and totals, counts, averages or whatever you wanted to calculate on top of column(s). So you will do with a single sheet at all. And when you want, you can also have some drop-down, where you can select what type of summary data you want, and have the according SUBTOTALS parameter to be calculated from this selection. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Kathrine J Wathne" wrote in message ... What I need help with is the following: I would like to have it so, that when I fill in information on one row in the main document (sheet 1), that the whole row (in sheet 1) automatically will be copy/pasted in the correct underlaying sheet per category (I have different sheets for different categories). In the main document (sheet 1) the information is divided into different product types in each row - however there are about 10 different product types / categories. When "product 1" is put into the main sheet it would be nice if this automatically is shown under the sheet "summary for product 1". Doing so enables me to have a correct sub-total per category which can eventually be linked to the "total-sheet". Any suggestions on how to do this correctly? Hope you understand my question :-) Kathrine |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Link from webpage to excel sheet | Excel Discussion (Misc queries) | |||
Marcro recroding/ stop Ikon not appearing in middle of excel sheet | Excel Discussion (Misc queries) | |||
Periodically exporting Excel sheet to HTML | New Users to Excel | |||
Starting MS Excel with a worksheet other than SHEET 1 | Excel Worksheet Functions | |||
make an excel worksheet (sheet 2) open w/ the cursor located in t. | Excel Discussion (Misc queries) |