Due Date, multiple return values.

[JapanDave]

I am trying to make a formula to lookup people with due dates 60 days or less, which would mean that multiple returns values will occur. The cells that don't return anything need to stay blank.

I have attached a smaller version of the file and the blank table in the range "B6:I15" is where I need the data to appear. There are two tables to return values from.

[TonyB51]

Hi, Dave

I have Added a new Column A so that a Vlookup can be used in cells B6 to J15.

See what you think.

Cheers

TonyB

[JapanDave]

Hey Tony,

Thanks for the help. It looks like I have not explained myself correctly again.
The due dates are in column "F", so I only need the data of those people who fall 60 days or less show up automatically in the range B6 to J15.

I am not sure how I am supposed to use that extra column you made for me?

[JapanDave]

This has stumped me. I have no clue about dates???

[JapanDave]

Any ideas, I really do need this.

Thanks guys.

[MarvinP]

Hi JapanDave,

Two hints on this problem.

Today() + 60 is part of the formula you need (I think).

By using Merged Cells you can't do Advanced Filters. Unmerge the CDE in all rows. Don't use Merged Cells as they make things harder !!

[JapanDave]

Thanks Marvin, I redid the file and the due dates are in the "D" column, not the "F" column, stupid me.

I was thinking I could use this formula to return a value of the time remaining, K1 being todays date.

Please Login or Register  to view this content.

but I can't get my head around how I can lookup all people with the value less than 60. On top of that it will have to give multiple return values, which I am guessing may need some kind of indirect formula.

Anyway here is the new file.

[MarvinP]

Hi Dave,

Find the attached with a possible sample of what you want. This is a Birthday Card List for upcoming birthdays. It shows who is going to have birthdays in the next 60 days. I hope this example helps with your problem.

[JapanDave]

Hey Marvin,

I modified your file to show time remaining until due dates, but when I hit refresh, the pivot table stops functioning.

Also is there a way to have the data show in the formate that I originally had in my file? I could use a helper sheet and have the data show in the layout that I inserted to the right of the pivot table. I have no clue how you were able to get the pivot table to filter the way you did.

[MarvinP]

Click the dropdown to the right of Row Labels. Then "Values Filter" to only show values less than a certain date. The sort by the values in the pivot table.

I hope that lets you find the Pivot dropdown.

[JapanDave]

Ahhhhh , that did the trick. Thanks.

One last question, is there some VBA code that will continuously refresh the pivot table when an entry is changed in the Data Range?

[MarvinP]

I think it is just as easy to do a Pivot Table Refresh All.

You could record a macro to Refresh All and then put it on a worksheet event, but this all gets more complicated. Are you sure you want to dive into event macros behind worksheets?

[JapanDave]

I found this code, but it does not want to work for me.

Please Login or Register  to view this content.

Edit: Posting before seeing your comment.

The problem is that this is for computer illiterate, so everything needs to be automated, unfortunately.

[TonyB51]

Hi, Dave

I thought that you wanted to retrieve a listing of people with less than 61 days.

I believe that the amendments to your sheet achieved that.

I have added comments to help you understand the VLOOKUP formula that I used.

There was only one Birthday that met the criteria.

I am not sure what other result you may be seeking.

Cheers

TonyB

[JapanDave]

Thanks Marvin and Tony.

Tony that makes sense now. Thankyou very much.