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

[bkucharski]

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

[mariosv]

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.

[bkucharski]

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?

[bkucharski]

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.

[mariosv]

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

[bkucharski]

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.

[bkucharski]

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

[bkucharski]

=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

[bkucharski]

=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?

[bkucharski]

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”))