How do I transfer totals from 1 sheet to another

September 16, 2015 at 11:59:10
Specs: Windows 7
I have a weekly spreadsheet (1 sheet for each day of the week and a total sheet for each day). I want to know a formula that can be used to transfer the daily total by persons name from the day of the week sheet into the total sheet. The formula would need to allow for changes made within the day of the week sheet.

See More: How do I transfer totals from 1 sheet to another

Report •

#1
September 16, 2015 at 12:14:46
I'm a little confused.

You said: "1 sheet for each day of the week and a total sheet for each day"

Wouldn't a "total sheet for each day" be the same as "1 sheet for each day"?

What I am missing?

In addition, we are going need some idea of the layout of your worksheets before we can offer any help. If you click on the following line, you will find instructions on how to post example data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Perhaps some examples of input data along with the desired result would help us help you. Don't forget to include Row numbers and Column letters as shown in the examples.

message edited by DerbyDad03


Report •

#2
September 16, 2015 at 12:19:44
Without knowing how your sheets are set up, the best I can offer is something like:

The Monday Total for John is in cell X1,
so on the Total Sheet,
Johns name is on Row 10
so in cell B10, use a formula like:

=Monday!X1

This will automatically copy any data in cell X1 on the Monday sheet
to the Total Sheet, Cell B10

MIKE

http://www.skeptic.com/


Report •

#3
September 16, 2015 at 12:45:14
Mike,
We are using that formula but because the daily sheet (see 1st table) will change from month to month that formula does not change with the weekly total sheet (see 2nd table).
<Name Timesheet # #/hrs Contract # # Pieces PRamt HRamt Total SUBTOTAL
Sam 107 0.25 6403 25 0.0228 0.00 $0.57
Sam 107 0.25 6403 30 0.0228 0.00 $0.68 1.25
Susie 105 0.25 6511 100 0.0200 0.00 $2.00
Susie 127 0.25 6513 78 0.0100 0.00 $.78 2.78

<NAME "Sat" "Sun" "Mon" "Tues" "Wed" "Thurs" "Fri" TOTAL
Sam 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.0000
Susie 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.0000
>


Report •

Related Solutions

#4
September 16, 2015 at 12:57:16
Thanks DerbyDad,

Sorry for the confusion...hope this helps explain it better.
The daily sheet (see 1st table) and the weekly total sheet (see 2nd table).
<Name Timesheet # #/hrs Contract # # Pieces PRamt HRamt Total SUBTOTAL
Sam 107 0.25 6403 25 0.0228 0.00 $0.57
Sam 107 0.25 6403 30 0.0228 0.00 $0.68 1.25
Susie 105 0.25 6511 100 0.0200 0.00 $2.00
Susie 127 0.25 6513 78 0.0100 0.00 $.78 2.78

<NAME "Sat" "Sun" "Mon" "Tues" "Wed" "Thurs" "Fri" TOTAL
Sam 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.0000
Susie 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.0000
>

What we want to do is to take the subtotal from the daily sheet and transfer that into the weekly total sheet for both Sam and Susie. Because the daily sheet will change month to month the following formula does not pick up the new cell #

The Monday Total for John is in cell X1,
so on the Total Sheet,
Johns name is on Row 10
so in cell B10, use a formula like:

=Monday!X1


Report •

#5
September 16, 2015 at 13:36:21
Did you not see my request to read (and use) the instructions on how to post example data in this forum? I'll try again.

Click on the following line:

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
September 17, 2015 at 08:05:37
Still not sure what it is you want,
but from what I can see on your sheets,
it would appear you want a cumulative total.

You have two Sam's on what I think is the Monday Sheet,
so to get both subtotals copied to the Weekly Total sheet,
something like:

=SUMIF(Monday!A1:A5,"Sam",Monday!B1:B5)

Where B1 thru B5 are the Subtotals cells.

Please follow DerbDad03 request, and read the HOW-TO he posted,

MIKE

http://www.skeptic.com/


Report •

#7
September 17, 2015 at 11:52:50
The 1st table is our 'daily sheet'
DATE:  MONDAY SEPTEMBER 14, 2015															
Name	TS #	# hrs	Cont#	#Pcs PR  HR	Tl  SUBTOTAL
Sam	107	0.25	6503	50 0.0167 $0.00	$0.84	
Sam	107			   0.0000 $0.00	$0.00	
Sam Total						$0.84
Susie	105	0.25	6514	70 0.0100 $0.00	$0.70	
Susie	105	0.25	6601	   0.0000 $7.25	$1.81	
Susie	127	0.50	6514	80 0.0100 $0.00	$0.80	
Susie	127			   0.0000 $0.00	$0.00	
Susie Total						$3.31

The 2nd table is the weekly 'daily' sheet where we want the 'daily' subtotal to transfer. Where we are having issue is when we have to make monthly changes within the daily sheet which may cause the subtotal line number to change, the formula in the weekly 'daily' sheet does not acknowledge the new line number.

2015								
NAME	"Sat" "Sun" "Mon" "Tues" "Wed" "Thurs" "Fri"  TOTAL
Sam	0.00  0.00  0.84  0.00   0.00	0.00	0.00  0.0000
Susie	0.00  0.00  3.31  0.00   0.00	0.00	0.00  0.0000


Report •

#8
September 17, 2015 at 13:29:53
Where we are having issue is when we have to make monthly changes within the daily sheet which may cause the subtotal line number to change, the formula in the weekly 'daily' sheet does not acknowledge the new line number.

No sure what you mean.

Is there more in the Daily sheet then what you have posted?

What are the actual names of your sheets?

Please understand that I have no idea what your sheets look like,
what formulas your using,
nor do I have any idea what it is your doing.
So this may take a bit of time.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#9
September 28, 2015 at 03:11:06
Mike,

What I really need to get some information on....is there a formula that can be used in one worksheet (weekly 'daily' sheet ) that references other worksheets (daily sheet)
in the same workbook but can allow if changes have to be made (row additions or deletions) to the 'daily sheets'??


Report •

#10
September 28, 2015 at 03:15:59
The 1st preview is our 'daily sheets'. We have Monday thru Sunday.
DATE:  MONDAY SEPTEMBER 14, 2015															
Name	TS #	# hrs	Cont#	#Pcs PR  HR	Tl  SUBTOTAL
Sam	107	0.25	6503	50 0.0167 $0.00	$0.84	
Sam	107			   0.0000 $0.00	$0.00	
Sam Total						$0.84
Susie	105	0.25	6514	70 0.0100 $0.00	$0.70	
Susie	105	0.25	6601	   0.0000 $7.25	$1.81	
Susie	127	0.50	6514	80 0.0100 $0.00	$0.80	
Susie	127			   0.0000 $0.00	$0.00	
Susie Total						$3.31


The 2nd table is the weekly 'daily' sheet where we want the 'daily' subtotal to transfer. Where we are having issue is when we have to make monthly changes within the daily sheets which may cause the subtotal line number to change, the formula in the weekly 'daily' sheet does not acknowledge the new line number. 

2015								
NAME	"Sat" "Sun" "Mon" "Tues" "Wed" "Thurs" "Fri"  TOTAL
Sam	0.00  0.00  0.84  0.00   0.00	0.00	0.00  0.0000
Susie	0.00  0.00  3.31  0.00   0.00	0.00	0.00  0.0000


 


Report •

#11
September 28, 2015 at 03:37:34
Please do not include the text of your question within the pre tags. As you can see from your most recent post, that makes it very difficult to read.

Only your data should be formatted with the pre tags.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
September 28, 2015 at 07:59:50
is there a formula that can be used in one worksheet (weekly 'daily' sheet ) that references other worksheets (daily sheet)
in the same workbook but can allow if changes have to be made (row additions or deletions) to the 'daily sheets'??

It may be possible, but a MACRO would be the better way.

Unfortunately, my Macro skill are just above nil.

MIKE

http://www.skeptic.com/


Report •

Ask Question