CALC: Reference Cell Data on different Sheets based Day of the Week

I am working in LibreOffice Calc v5.3.0.3 and I am looking for some assistance on being able to Reference/Return Cell Data on one Sheet based upon the Day of the Week (Sun, Mon, etc).

I have multiple Sheets that range: WEEK 1, WEEK2, … WEEK 12, WEEK 13 that contains sales data for that specific Week. Contained within each week’s sheet are the following Columns: Day of the Week (A7:A14), Daily Sales (N7:N14) and Number of Customers (BH7:BH14).

On a separate Sheet called WTD, I would like to Reference, based on the current Day of the Week, the Sales and Number of Customers for that specific day of that week.

I am using a Cell on the WTD Sheet called Current Week (WTD.I8) that contains an Integer representing the week number.
Example: 3
Entering an Integer between 1 and 13 allows me to Reference Specific Sheets based upon this number.

I utilize this integer along with functions such as INDIRECT.
Example: =INDIRECT(“Week “&WTD.$I8-1&”.N14”)
In the above example it will return the Previous Weeks (Week 2) Total Sales (N14) since I8 = 3

I am stumped on finding a way to Return a Cell value based upon the Current Weeks number and Day of the Week.
Example:
Current Week: 3
Today’s Sales - Last Week: $XXXX (Returns the Value on Sheet: Week 2 for today (Wed).

I hope that I have not made this too confusing to understand.

Thanks for reading…

Bernie

A formula like
=SUMPRODUCT(INDIRECT("Week"&WeekNumber&".A1:A14")=Day_of_week;INDIRECT("week"&$I$8&".B1:B14"))
should work.

Having all data in only one table it’s the better way to facilitate their analisys, so the specializated functions can work without use the INDIRECT() function.

Thanks for the quick reply. I appreciate your time in developing and providing a formula to assist my issue.
I took your formula and modified it based upon my data cells/range and came up with the following:
=SUMPRODUCT(INDIRECT(“Week “&WTD.$I8-1&”.A7:A13”)=day_of_week,INDIRECT(“Week “&WTD.$I8-1&”.N7:N13”))

Does DAY_OF_WEEK exist in LibreOffice v5.3.0.3?
I am receiving the following error: #NAME?

I8 contains an integer for the Week Number that I use to Reference the Specific Sheet Number: "Week "&WTD.$I8-1

A7:A13 contains the values of: Sun, Mon, Tue, Wed, Thur, Fri, Sat on week Sheet from: Week 1, Week2…through Week 13.

N7:N13 contains the Sales Data that I would like returned for whatever is the current day. Today would be Thursday so it should return the Data from N11 since A11 contains the value of: Thur

Thanks again.

I think the easier is putting the day of week in a cell, and replace in the formula day_of_week for cell reference.

Thanks for the direction to look into for my solution. I am going to try and incorporate the following formula:
=WEEKDAY(TODAY(), 1)
If I then format the cell so that it returns (Sun, Mon, Tue, Wed, Thur, Fri, Sat) I should be able to create a IF:THEN statement to get the data from that cell. I’ll let you know what I come up with.

Ok I tried using into using two different functions: IFS and CHOOSE
Here is a list of the variables:
B6 = Display the current day of the Week. Its formatted to display the day of the week as follows: Sun, Mon, Tue, Wed, Thur, Fri, Sat using this formula:
=WEEKDAY(TODAY(), 1)
I6 = The current Week Number in our Sales Cycle. This can range from 1-13. We can enter any week number here
WEEK 1, WEEK 2, WEEK 3…WEEK 13 = These are the Sheet Tabs with each weeks sales data

=IFS Function
Example: Today is Tuesday, September 6th of Week 6
I6 = 6
B6 = Wed using the formula =WEEKDAY(TODAY(), 1)
=IFS(B6=“Sun”,INDIRECT(“Week “&I6-1&”.N7”),B6=“Mon”,INDIRECT(“Week “&I6-1&”.N8”),B6=“Tue”,INDIRECT(“Week “&I6-1&”.N9”),B6=“Wed”,INDIRECT(“Week “&I6-1&”.N10”),B6=“Thur”,INDIRECT(“Week “&I6-1&”.N11”),B6=“Fri”,INDIRECT(“Week “&I6-1&”.N12”),B6=“Sat”,INDIRECT(“Week “&I6-1&”.N13”))

The above Function returns #N/A

=CHOOSE Function
=CHOOSE(WEEKDAY(TODAY()),INDIRECT("Week "&I6-1&"N7,INDIRECT(“week”&I6-1&"N8,INDIRECT("Week "&I6-1&"N9,INDIRECT(“week”&I6-1&"N10,INDIRECT("Week "&I6-1&"N11,INDIRECT(“week”&I6-1&"N12,INDIRECT("Week "&I6-1&“n13”)))))

The above function returns Err:508

I think I’m closer…Any ideas?

I think I found my answer and issue.

I have cell I6, which had the formula: =WEEKDAY(TODAY(),1)
It returned a value of 4 for today (Wednesday). My issue is that I formatted the cell so that instead of 4 it displayed Wed.

If I leave Cell I6 unformulated so that it just displays an integer the following function appear to work:

=CHOOSE(C6,INDIRECT(“Week “&I6-1&”.N7”),INDIRECT(“Week “&I6-1&”.N8”),INDIRECT(“Week “&I6-1&”.N9”),INDIRECT(“Week “&I6-1&”.N10”),INDIRECT(“Week “&I6-1&”.N11”),INDIRECT(“Week “&I6-1&”.N12”),INDIRECT(“Week “&I6-1&”.N13”))