Previous Topic Next Topic

DUE DATE REMINDER IN EXCEL TO NEW SHEET?

Solved/Closed
Babu546 Posts 7 Registration date Tuesday January 20, 2015 Status Member Last seen January 26, 2015 - Jan 21, 2015 at 08:57 AM
 Babu546 - Jan 22, 2015 at 01:37 PM
Hello,


I Have a problem in this code

=INDEX(Master!$B$3:Master!$B$1067,MATCH(TODAY()+30,Master!$H$3:Master!$H$1067,0))

HERE I HAVE COLUMN " B " REPRESENTS " SOL_ID " WHICH IS A UNIQUE IN MASTER SHEET,

HERE COLUMN RANGE IS BETWEEN A3 TO H 1067

COLUMN " H " REPRESENTS THE DUE DATE FOR THE SOL_ID

NOTE: SAME DUE DATE IS THERE FOR MORE THAN ONE SOL_ID

WHEN I RUN THIS ABOVE CODE IT IS GIVING ONLY ONE SOL_ID BUT I WANT TOTAL SOL_IDS WHOSE DUE DATE IS AFTER 30 DAYS IN NEW SHEET

NOW MY MOTIVE IS TO PRINT/PASTE THE SOL_ID WHOSE DUE DATE IS AHEAD OF ONE MONTH IN ANOTHER SHEET

FOR EXAMPLE :-
I HAVE 3 SOL_IDS 522,645 & 1601 DUE ON AFTER 30 DAYS i.e =TODAY()+30

NOW I WANT TO PRINT/PASTE THESE IN TO ANOTHER SHEET SO THAT I CAN USE THESE UNIQUE SOL_IDS IN VLOOKUP AND GET THOSE ROWS IN NEW SHEET

I SAID THAT I AM GETTING ONLY ONE SOL_ID AND WHEN I USED IT IN VLOOKUP IT IS GIVING ME THE ERROR " A VALUE IS NOT AVAILABLE TO THE FORMULA OR FUNCTION "

ANY ASSISTANCE FOR THIS ISSUE IS GREATLY APPRECIATED....
THANKS IN ADVANCE


Related:

1 response

Answer 1 / 1
RayH
Jan 21, 2015 at 03:27 PM
That formula was only designed to give in a single result.

Rather that using a formula it is probably best to using a macro to filter the data instead. 


Sub filterandcopy()
Dim dateoffset As Integer
Dim lastrow As Long

lastrow = Worksheets("MainSheet").Range("A" & Rows.Count).End(xlUp).Row

dateoffset = 30

d = Date + dateoffset

    Worksheets("MainSheet").Range("$A$1:$H$" & lastrow).AutoFilter Field:=8, Criteria1:=">=" & d, Operator:=xlAnd
    Worksheets("MainSheet").UsedRange.Copy Destination:=Sheets("DueDate").Range("A1")

    Selection.AutoFilter
    
End Sub


In this example I have named the source data "MainSheet" and the sheet to where the data is pasted, "DueDate".

I have used a variable for the dateoffset, in this case the value is 30, it could be changed to some other value if needed. It could even be set to refer to a cell on a sheet that can be changed as and when required to save messing around with the code each time a change is required.

Just something to be aware of, the use of "UsedRange" could be problematic as it does select blank cells to the right and bottom if they have been used at some point.
1
Babu546 Posts 7 Registration date Tuesday January 20, 2015 Status Member Last seen January 26, 2015
Jan 22, 2015 at 03:20 AM
thank a lot sir for your response

But I am a beginner to Macros so I thought I will share my file with you so that I may explain you better
this is my file link

http://speedy.sh/q658J/Due-Date-Reminder.xls

any assistance will greatly appreciated....
Thanks in advance
0
RayH > Babu546 Posts 7 Registration date Tuesday January 20, 2015 Status Member Last seen January 26, 2015
Jan 22, 2015 at 11:06 AM
The original code worked.
I have made a couple of 'enhancments' to help you.

http://speedy.sh/YB26j/babu-Due-Date-Reminder.xls
0
Babu546 Posts 7 Registration date Tuesday January 20, 2015 Status Member Last seen January 26, 2015 > RayH
Jan 22, 2015 at 12:05 PM
thanks a ton for your effort to make me happy see you
0
Babu546 > RayH
Jan 22, 2015 at 01:37 PM
I very happy happy with your response mr.RayH
Thanks a lot
0