Formula that will change which cells are added depending when data is entered

[Luke1690]

Hi Guys,

i have a data entry sheet and a weekly summary sheet.

The data entry sheet is separated into days Mon,Tue,Wed,Thu,fri. within each day section i enter two lots of data called Buffer Totals for that day.

The weekly summary sheet shows what we finished on, So fridays buffer totals added together. (Not a total for the week)

Now, if we don't work on Friday obviously the weekly summary shows 0 because there wasn't any data entered.

how can i get the weekly summary to show the total for each day but overwrite the previous day when the next day is entered.

 

[awoohaw]

are you saying that in the Friday column you want to see a total of Mon-Thur if there is no data for Friday?
You can't have data entry and a formula in the same cell.
If it is after processing on Monday, do you want the Monday total so show on the Tue - Fri cells?

Why not have a data entry page, and then a report page?

Here is a great video on why data collection and reporting should be separate, it is very recent:

 

[Luke1690]

Hi,

Thanks for getting back to me.

i do have a report sheet and a data entry sheet.

i want the report sheet to show a total of the two values for Monday then when Tuesdays data is entered i want the report to show a total of the two values for Tuesday. Not a combined total of Monday and Tuesday. So when Wednesday data is entered the report would only show a total for the two values for Wednesday, basically cancelling the previous day out.


hopefully that makes more sense ill see if i can knock up a draft to show you an example if not.

 

[awoohaw]

Yes, an example would be more than helpful. If you can use the xl2bb add in (link is below as well as in the post formatting tool bar).
But, my interpretation of what you want is something like this, although I've put it all in one worksheet with two different weeks (so I'm assuming you have data and reports for indvidiual weeks?)

Book1
	A	B	C	D	E	F	G
1	data sheet					Weekly Report Sheet
2	Date	Value				Week Ending	Value
3	Mon 2024-04-01	7				Fri 2024-04-05	10
4	Mon 2024-04-01	8
5	Tue 2024-04-02	9
6	Tue 2024-04-02	6
7	Wed 2024-04-03	7
8	Wed 2024-04-03	9
9	Thu 2024-04-04	8
10	Thu 2024-04-04	5
11	Fri 2024-04-05	5
12	Fri 2024-04-05	5
13
14
15	data sheet					Weekly Report Sheet
16	Date	Value				Week Ending	Value
17	Mon 2024-04-01	7				Fri 2024-04-12	0
18	Mon 2024-04-01	8
19	Tue 2024-04-02	9
20	Tue 2024-04-02	6
21	Wed 2024-04-03	7
22	Wed 2024-04-03	9
23	Thu 2024-04-04	8
24	Thu 2024-04-04	5
25	Fri 2024-04-05
26	Fri 2024-04-05
Sheet1

Cell Formulas
Range	Formula
G3	G3	=SUMPRODUCT((F3=$A$3:$A$12)*($B$3:$B$12))
G17	G17	=SUMPRODUCT((F17=$A$17:$A$26)*($B$17:$B$26))

 

[Luke1690]

 

[Peter_SSs]

Is this what you are after?

24 04 04.xlsm
	A	B	C	D	E	F	G	H	I
1
2		Mon	Tue	Wed	Thu	Fri
3		6	1	8					8
4		1	8	3					3
Last entry

Cell Formulas
Range	Formula
I3:I4	I3	=LOOKUP(9^9,B3:F3)

 

[Luke1690]

hi,

Peter that is exactly what i want thanks for your help.

Just one more question, if i wanted to show the two data values Buffer 1 and Buffer 2 added together as total on the weekly report, how would i do that?

 

[Peter_SSs]

Cheers. Glad it helped.

, if i wanted to show the two data values Buffer 1 and Buffer 2 added together as total on the weekly report, how would i do that?

Here are three options.

24 04 04.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2		Mon	Tue	Wed	Thu	Fri
3		6	1	8					8	11
4		1	8	3					3	11
5									11
Last entry

Cell Formulas
Range	Formula
J3	J3	=LOOKUP(9^9,B3:F3)+LOOKUP(9^9,B4:F4)
J4	J4	=SUM(INDEX(B3:F4,0,COUNT(B3:F3)))
I3:I4	I3	=LOOKUP(9^9,B3:F3)
I5	I5	=SUM(I3:I4)

 

[Luke1690]

=LOOKUP(9^9,B3:F3)+LOOKUP(9^9,B4:F4) Thanks mate.

 

[Peter_SSs]

You're welcome.