Needed; Formula to trigger a row to copy to specified worksheet at the end of the same workbook

[Nita Scott]

I have several work sheets that have salary, purchases, travel instate, travel out of state... that I populate for the accounting purpose. Each row entry is made for an individual or department. At the end of the workbook each individual or department has their summary page that will show only what happened with them with totals.
What I need is a formula that when I enter the initial of that individual or department it will trigger to copy that row to the appropriate summary page. Can you help?

 

[Mike De Salvo]

Can you attach a sample?

 

[Mike De Salvo]

Instead of applying a macro, you can use a "SUMIF()" function for each department or use "IF/THEN" function statements to get to the same result. Would probably be faster. For Example:

=IF(A1="M",SUM(A1:Z1),0)

 

[Nita Scott]

=IF(Sheet1!C13="RP",SUM(Sheet3!A13:N13),0) So I did something wrong when I typed the initals RP in sheet 1 cell C13 the formula produced the number 0 in sheet 3 row C13 instead of copying the information in sheet1 row13 to row 13 in sheet 3. Can you tell where the error is in the formula at the front of this message? Many thanks!!

 

[Mike De Salvo]

I noticed that you are referencing Sheet1, but summing cells in Sheet3, not in the same Sheet1. Try This:


=IF(Sheet1!C13="RP",SUM(Sheet1!A13:N13),0)

Another option is to sum the rows in Sheet1 in Column M and then apply the following formula:

=SUMIF(Sheet1!C1:C50,"RP",Sheet1!M1:M50)

 

[Nita Scott]

Will this formula product (copy) the row verbatim from sheet 1 to sheet 3? I am new to the forum and need to see how to attach so I can show you the actual snipit of the workbook, I will try to find that and get back. Thank you for taking your time to help me I really appreciate it!

 

[Mike De Salvo]

The SUMIF() formula just takes the sum total, it will not copy the each indivual row value. If you are looking to copy the entire row with details to each summary sheet and not the the sum totals, then the above formuals will not work for your needs. For example, If Sheet1 shows something like this:

[TABLE="width: 288"]
<COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]Class[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]C[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]D[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]SUM[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RP[/TD]
[TD="class: xl65, bgcolor: transparent"]50[/TD]
[TD="class: xl65, bgcolor: transparent"]60[/TD]
[TD="class: xl65, bgcolor: transparent"]780[/TD]
[TD="class: xl65, bgcolor: transparent"]50[/TD]
[TD="class: xl65, bgcolor: transparent"]940[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]PP[/TD]
[TD="class: xl65, bgcolor: transparent"]30[/TD]
[TD="class: xl65, bgcolor: transparent"]20[/TD]
[TD="class: xl65, bgcolor: transparent"]5[/TD]
[TD="class: xl65, bgcolor: transparent"]100[/TD]
[TD="class: xl65, bgcolor: transparent"]155[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]AP[/TD]
[TD="class: xl65, bgcolor: transparent"]100[/TD]
[TD="class: xl65, bgcolor: transparent"]75[/TD]
[TD="class: xl65, bgcolor: transparent"]50[/TD]
[TD="class: xl65, bgcolor: transparent"]60[/TD]
[TD="class: xl65, bgcolor: transparent"]285[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RP[/TD]
[TD="class: xl65, bgcolor: transparent"]80[/TD]
[TD="class: xl65, bgcolor: transparent"]90[/TD]
[TD="class: xl65, bgcolor: transparent"]80[/TD]
[TD="class: xl65, bgcolor: transparent"]100[/TD]
[TD="class: xl65, bgcolor: transparent"]350[/TD]
[/TR]
</TBODY>[/TABLE]


The SUMIF() function would just take the sum total of 940 + 350 = 1,290 and report the 1,290 value only. If you want the row detail to show up like this on the Summary Page:

[TABLE="width: 240"]
<COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY>[TR]
[TD="width: 64, bgcolor: transparent"]Class[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]A[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]B[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]C[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent"]D[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RP[/TD]
[TD="class: xl65, bgcolor: transparent"]50[/TD]
[TD="class: xl65, bgcolor: transparent"]60[/TD]
[TD="class: xl65, bgcolor: transparent"]780[/TD]
[TD="class: xl65, bgcolor: transparent"]50[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]RP[/TD]
[TD="class: xl65, bgcolor: transparent"]80[/TD]
[TD="class: xl65, bgcolor: transparent"]90[/TD]
[TD="class: xl65, bgcolor: transparent"]80[/TD]
[TD="class: xl65, bgcolor: transparent"]100[/TD]
[/TR]
</TBODY>[/TABLE]


Then you would need to write a macro that would only copy rows with the RP class and transfer them to the correct summary sheets.

 

[Nita Scott]

Oh my, I haven't done any work with macros, and that is exactly what I need. I tell you the more of work in excel the more I love it but the dumber I feel.