1

I am trying to complete this command (this would be a cell value on sheet 2): A1 (sheet 1) if K(sheet 1) is current date

Basically I want cells on sheet 2 to contain data from sheet one, only if Col K on sheet one has the current date.

Is there an actual formula to complete this action?

EDIT for claification: I want to get all items on sheet 1 with the current date in column K to report to sheet 2 as a daily report. I made the error of referencing cell A1. That was just the starting point. Basically if it is on sheet 1 with the current date, it will move that line to sheet 2 as a consolideted report with no lines skipped for the lines that do not have the current date.

Basically how do I get sheet 2 to not display "false" results.

6
  • Perhaps you may want to look at using a filter instead of copying your data to another sheet? See the Excel Data/Filter menu.
    – Lord Peter
    Sep 18, 2012 at 18:42
  • I have a workbook with different sheets used for different people/purposes. I have to move all the current dated items to another sheet. I can filer by current date and copy and paste...but I want excel to do that for me :)
    – Pete
    Sep 18, 2012 at 18:59
  • Ok, let's try something else. What kind of data is in sheet 1 exactly? Can you give some examples? Are there several days in it? One day per line or several lines per day? Do you want only the lines of today from sheet 1 to show up on sheet 2 in the corresponding lines with blanks in between? Or do you want it to be condensed so that there are no blanks?
    – simbabque
    Sep 18, 2012 at 19:07
  • Re the false: Put the else-part of the IF() back in. It's basically IF(condition ; then ; else). Since you want it to not show false IF the date is different, put what you want it to show in the else-part. That should be, well, "" as in an empty string.
    – simbabque
    Sep 18, 2012 at 19:08
  • Ok I'm really starting to understand this :) Thank you. I put the "" back in and i see why it's needed. It did work. Is there a way to auto hide blank lines? For the first question sheet one has multiple lines per days. It's basically realtionships that are requested and when they complete I input the current date, so several lines can complete in a day and belong on the second sheet. I want condensed no blanks.
    – Pete
    Sep 18, 2012 at 19:29

1 Answer 1

1

I need to do this in part-German, but nevermind.

So in Sheet 1 I have Stuff in A1 and =TODAY() in K1 (which looks like 17.09.12 to me, but nevermind). In Sheet 2 I now add the following line of code to A1:

=IF(Sheet1!K1=TODAY();Sheet1!A1;"")

That's all there is. It will get you the Stuff from Sheet 1 on Sheet 2.

This of course also works if you write the date in Sheet1!A1 yourself. It does not check for the string =TODAY(), but for the value.


Note that you can also create the formula by clicking on the cells and even the sheet tabs at the bottom of the screen. It will place the correct names in your formula for you.

7
  • You normally have ! after the sheet name, so for my English settings that would be this version of the formula =IF(Sheet1!K1=TODAY(),Sheet1!A1,"") Sep 17, 2012 at 20:41
  • @barry You're right, thanks. That's not even German. I had to do it in Libre Office. Changed.
    – simbabque
    Sep 18, 2012 at 7:47
  • It's not working for me. It keeps highlighting the () after the word today. I tired to do it manual by typing =IF( and then clicking sheet 1 K!..etc with no luck.
    – Pete
    Sep 18, 2012 at 15:48
  • I got it to work, I just got rid of everything after A1. Not sure what the "" was for?
    – Pete
    Sep 18, 2012 at 17:05
  • Hello, I need more HELP :) I realized this does work, but not exactly right. I want sheet 2 to be a daily report of all items that have the current date in Col K on sheet 1. The way we did it, it's done by cell, so if row 3 and row 12 have the curent date, I get those two with a bunch of "false" rows in between. How can I get one consolidated report based on Colimn k's date?
    – Pete
    Sep 18, 2012 at 17:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .