Skip navigation

## How to display data from another cell if day is today?

393 Views 6 Replies Latest reply: Apr 28, 2013 6:38 PM by George Coghill
Level 2 (475 points)
Currently Being Moderated
Mar 9, 2013 12:51 PM

I am trying to pull info from one table into another, using "today" as a criteria.

I have a table of data that contains the total hours of sleep for each night by calculating the sleep and wake times. I'd like to add a seprate table that just shows me the data from last night (the total hours slept) on an "overview" page where I am pulling other data (like total miles walked and such).

I can't figure out the formula to do this. I have this so far:

``````=IF(TODAY(),Total Sleep,"")
``````

I'd like to be able to do the same for the past week, in a table that runs Sunday to Saturday, and auto-populates for the current week. Upcoming days would be blank, but it would show me how the days are progressing.

Thank you.

Mac Pro, OS X Mountain Lion (10.8.2)
• Level 7 (27,345 points)

Hi George,

Haven't seen you here for a while.

You need something to compare TODAY() with to create a Boolean expression for the IF statement. How about sharing your layout with us and we can suggest some programming to get you where you want to go.

Jerry

• Level 7 (28,740 points)

Hi George,

You've done most of the heavy lifting here. All that's left is to write a VLOOKUP formula to retrieve the values from the rows you want, then chart it.

Here's what it looks like with the new table and chart placed beside the Sleep Duration table on your Sleep Totals sheet.As your table doesn't include current data, I used a fixed date in place of TODAY():

Formulas in the small table:

A2, and filled down to A8: =A\$1-(8-ROW())

B2, and filled right to C2 and down to C8: =VLOOKUP(\$A,Sleep Duration :: \$A:\$C,COLUMN())

To get the current week, either enter =TODAY() into cell A1, or replace the cell reference to A\$1 with TODAY() in the first formula (before filling it down the column).

Chart uses the values in column C, and is the default vertical bar chart with two adjustments: Squeezed horizontally to fit better in the screen shot; labels on category axis rotated 90°.

Regards,

Barry

• Level 7 (27,345 points)

George,

Thanks for sharing your spreadsheet with us. Barry's suggestion looks appropriate for your needs. I notice that you have a provision for a Walked Miles Average. Is that going to be a Miles per Day average for the entire period covered by the data?

Jerry

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.