Ask Your Question
0

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

asked 2017-08-23 20:15:45 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted
0

answered 2017-08-24 01:15:11 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more

Comments

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 gravatar imagebkucharski ( 2017-08-24 19:32:00 +0200 )edit

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.

bkucharski gravatar imagebkucharski ( 2017-08-24 19:34:28 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2017-08-25 13:36:46 +0200 )edit

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 gravatar imagebkucharski ( 2017-09-06 19:04:24 +0200 )edit

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 gravatar imagebkucharski ( 2017-09-06 20:09:32 +0200 )edit

=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 gravatar imagebkucharski ( 2017-09-06 20:11:00 +0200 )edit

=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 gravatar imagebkucharski ( 2017-09-06 20:11:21 +0200 )edit
0

answered 2017-09-06 21:42:43 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-08-23 20:15:45 +0200

Seen: 216 times

Last updated: Sep 06 '17