Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Kathrine J Wathne
 
Posts: n/a
Default 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   Report Post  
Roger Govier
 
Posts: n/a
Default

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   Report Post  
Arvi Laanemets
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Link from webpage to excel sheet Auto-Reply to mail in folder, using a te Excel Discussion (Misc queries) 0 July 21st 05 09:08 PM
Marcro recroding/ stop Ikon not appearing in middle of excel sheet vishu Excel Discussion (Misc queries) 3 July 6th 05 11:37 PM
Periodically exporting Excel sheet to HTML SupportX New Users to Excel 1 July 4th 05 07:00 PM
Starting MS Excel with a worksheet other than SHEET 1 IGNACIO Excel Worksheet Functions 2 February 18th 05 07:29 PM
make an excel worksheet (sheet 2) open w/ the cursor located in t. animated text in excel worksheet Excel Discussion (Misc queries) 0 February 10th 05 11:29 PM


All times are GMT +1. The time now is 10:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"