IF... THEN... formula in Excel

I am not sure if this can be done.  I have an Excel sheet with various information, one of the columns is a follow up date.  I have put some conditional formatting so that today's date appears in read in that column.  However, the user would like to have all the rows that need to be followed up today appear on a separate sheet.
I was thinking that since I can do a formula such as =Sheet1!A3 is there no way to have an IF... THEN... formula that would do the =Sheet1!A3 IF C3 = today's date?
Thank you for your help
Jeannie
Jeannie CoteAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Hi Jeannie, welcome to Experts Exchange.

Are you aware of Auto Filter?

Select your Data Range and from the Data Tab select the Big Funnel button to Apply an AutoFilter. This will put a little DropDown box against each of the header items.

In your Follow Up Date column, select the dropdown and there will be a list of Filter Options and then the list of actual dates, probably grouped by month. One of the Filter Options will be "Date Filters" and then one of the Options for that will be Today. Select that and only items with Today's date will be visible. Each day you then just re-apply the filter to refresh the list.
0
Rob HensonFinance AnalystCommented:
If your User then wants those items in a separate sheet, select all the data as a block and copy to the clipboard. Go to another sheet and paste from the clipboard; only the visible items will have been copied.
0
LearnReporting Automation ExpertCommented:
Hi Jeannie Cote,

If you need macro to do that please let me know, if go with Advance filter option check out on this video "https://www.youtube.com/watch?v=AqvWhyRJvUo"

Thanks and Regards,
Learn Excel in Tamil
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

NerdsOfTechTechnology ScientistCommented:
I found this cool formula that can help do this it combines INDEX, SMALL, IF and ROW functions.

=IFERROR(INDEX($A$5:$B$11,SMALL(IF($A$5:$A$11=$E$4,ROW($A$5:$A$11)-4),ROW(A1)),2),"")

Open in new window


Example data
Although you could move the reference cell, in the example, E4 would hold the search value.

For more on how to use this, see: https://www.myonlinetraininghub.com/excel-factor-17-lookup-and-return-multiple-matches
0
Jeannie CoteAuthor Commented:
Thank you for your comments, I found the following formula =IF(Sheet1!$C3=TODAY(),Sheet1!A3,"") which I then copied on each row and select a filter so that only none blanks appears.  Finally I secured the second sheet on which the follow up list appears so that no one can make changes by mistake.
Thank you again for your time
0
Rob HensonFinance AnalystCommented:
So if you're using filter why not just filter the source data.
1
Roy CoxGroup Finance ManagerCommented:
Here's an example of using AdvancedFilter to do this with dates
AdvancedFilter_Dates_Example.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
By securing the sheet, I assume you mean Worksheet Protection. The standard Worksheet Protection settings do not allow use of AutoFilter but there is an option that can be enabled to allow it. In the Worksheet Protection dialogue, where you set the password etc, scroll down the list in the bottom half of the dialogue and towards the bottom of the list there is an option to allow "Use AutoFilter".

One downside to this method is that there is potential for data to be missed as the source data expands the second sheet has to be updated to keep track unless you overpopulate the second sheet to allow for future rows on the source sheet; if you have a lot of columns this can have an impact on file size and speed of calculation.

What data does your source sheet contain? Is it numeric or just text entries? Just thinking you might be able to use a Pivot Table to pull data from the source sheet for a specific date.
1
Jeannie CoteAuthor Commented:
Yes, I did secure the worksheet (sheet2!) with a password and checked all the boxes.  The user then clicks on the first filter to refresh the list which is not a calculated list, it is a list of prospective clients so it is populated by names, addresses, dates and various information.
I have never done a Pivot Table do you think that would be easier for the users?
Thanks
0
Rob HensonFinance AnalystCommented:
The whole point of Protection is to prevent people from doing things on the sheet but the tick boxes will allow certain actions. By ticking all the boxes you are as near damn it unprotecting the sheet; effectively all the user won't be able to do is delete stuff.

Can you upload a sample file with dummy data in the same layout as the real file? If so I will take a look and make a pivot table on it.
0
Jeannie CoteAuthor Commented:
Hi
the information in the spreadsheet is in French.  The date that I am talking about is in the "suivi" column
Thank you for your help
Jeannie
ListeProspects_test.xlsx
0
Rob HensonFinance AnalystCommented:
Sorry, it doesn't look like a Pivot Table would be suitable after all.

However, the Auto Filter and copy I suggested or Advanced Filter suggested by Roy Cox would be good for this. The fact that the headers are in two rows may cause an issue with Advanced Filter; Auto Filter and manual/automated copy shouldn't be too much of an issue as the headers aren't as crucial for Auto Filter.
0
Jeannie CoteAuthor Commented:
thank you for your time, it is appreciated
0
Roy CoxGroup Finance ManagerCommented:
The suggested solution is merely a link to video.

Rob Henson and myself have both offered solutions. My post includes an example of using AdvancedFilter which is what the video shows.

I would suggest 500 points Roy Cox and 500 points Rob Henson
0
NerdsOfTechTechnology ScientistCommented:
Both the reason left by LET of "Na" and the solution suggested is insufficient; thus, I concur with Roy Cox's vote and suggest:

Roy Cox #a42539471 (500 points, best solution)
Rob Henson #a42539723 (500 points, assisted solution)
1
NerdsOfTechTechnology ScientistCommented:
Auto Filter or Advanced Filter suggested should work. For additional filtering, a macro might be useful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.