identify and print the ones on the list

[GayleK]

This post is my first on this board and I am not sure if this is the correct area to ask or to which area I should post this request! So please tell me if I should post this in another area!
Here is the issue that I need LOTS of guidance to complete. I have used Excel very little in the past but think it is the program to use for what I need to do.
Have a list of approx. 125 people (for our church's prayer list). What I would like to do is have a formula/macro that will identify and print the ones on the list

-that have been on the list less than 2 months
-identify by printing one "*" by thier name when the 2 months will be up in 2 weeks
-identify by printing two "**" by the name when the person will be removed in one week.

Another way of stating what I am wanting Excel's functions to do is the following:
In one column, I would enter the date a person is added to the list, then I would want the formula/macro to calculate the date the person would be removed from the list. When the list is printed weekly for our bulletin, I would like the program to create the list/print only the persons who have been on the list less than two months. In addition, I would like for the ones that will be removed in 2 weeks to be identified with an asterick when the printing is done. Then 2 astericks when the person would be removed in one week.(This would allert people to update us on the person's condition and continued need for special prayer.)
Of course, I would like this calculation to be done automatically when the data base is opened. OR REQESTED. I'm not sure of my terminology here! I want the calculations to be done without me having to redo the database and formula/macro weekly.
I sure hope this request is clear!
Can you make recommendations on where I should place this request? Can this be done with a formula or does it need a maco? Which forum can help me learn what I want to accomplish?
Thanks so much! This forum appears to have people who know EXCEL very well and can help me learn. Thanks again.
Gayle

[WinteE]

Hi Gayle,

Please give some more information about the data you're using and what criteria you're using to define whether people should be removed from the list or not.

I guess you'll have to use IF statements in combination with 'Autofilter' to filter. It's possible to activate the 'Autofilter' everytime you activate the sheet.

Erik

[Richard Buttrey]

Hi,

If I've interpreted your request correctly, and assuming the dates you add a person start in B5, enter the following in C5 and copy down:

Please Login or Register  to view this content.

HTH

[GayleK]

Thank you for your responses!
Here is additional information that might help you understand what we are attempting to do! and BTW, I am using Excel 2007.
A person's name is submitted to be added to the prayer list say on July 11, 2008.
We would like to have the program calculate 2 months from the date the person is added to the list and this would be the date the person would be scheduled to be removed from the list (Sept.12,2008) (Each person probably would have a different date that they are added to the list.)
We would like to have an "*" printed by the person's name when it is 2 weeks from the time that the person's name is scheduled to be removed from the prayer list. And "**" when it is one week prior to the name being removed.
These astericks would tell the congregation that the person will be removed from the list unless the church is called to let us know that the person is still in need of special prayer. (The info on the meaning of the "*" would be printed on the prayer list that is distributed in the bulletin.) We, the church staff do not know all the people on the list because many do not attend our church.Therefore, we wanted to give the congregation an opportunity to "renew' the prayer request. If they do not call to ask us to cont. prayer, the person's name will be removed from the list. (We have had, on rare occassions, the names of expired individuals on the list for quite some time- thinking they were perhaps in a nursing home or long term care facility!) If a request comes in to continue to pray for the person, the date added to the list would be updated to the current date and the process would be repeated.
We would like it set up so that each week, the program would calculate the infomation and when printed would print only the names that the 2 months time frame has not expired and that it would print the "*" or "**" by the appropriate names for alerting purposes. I sure hope this makes sense!
I do appreciate any and all help!
TIA!

[martindwilson]

i dont mind having a look,but this is a bit sad.automating prayer lists whatever next!

[GayleK]

I totally agree that it is sad to "automate" a prayer list but it is difficult to manually update a list of 125 plus names weekly when we don't know many of them. And we really do use the prayer list. There are many needs that are in the world and many blessings also! (BTW, I did start to set up a different situation to ask the question but decided to use what I was trying to do...so I wouldn't get confused!)
And I do thank you for taking a look.
Gayle

[Richard Buttrey]

Thank you for your responses!

.........I do appreciate any and all help!
TIA!

So did you try the formula I suggested, and was it what you want?

Rgds

[martindwilson]

try attached namein a2 date in b2
week is counted from sunday to sat

[GayleK]

Richard, Sorry that I didn't respond. Yes, I tried the statement that you gave me. I placed it in a trial list sheet. What then showed was today's date and in the next column **. Did I do it correctly? Should it have showed the date at the end of the two months? or other info? And I do appreciate the info It got me further than I was before!! It may be exactly what I need and I don't know it yet. Please read on!!
Martin, that appears to be getting there also. But the end date doesn't show and the column C shows ** and the print does show Mr. Notsowell**.

Richard, Martin, Please remember I have minimal experience with Excel. Am I doing something wrong??? How do I or where should I see the ending date of each persons 2 months? And the one week before the end and the two weeks before the end of the 2 months? How do I execute (is that the correct term?) the formula to get only the names of the persons that fall within their 2 month date, including the ones with one * and two **printed? Does that make sense? Am I expecting too much?? I would appreciate feedback/help on these things.
I found the links to tutorials and will be doing quite a few of them! I will start at the beginning! Is there one that would specifically help me with this list that you are aware off?
Thanks!
Gayle

[WinteE]

Hi Gayle,

I've made you an example. In row 2 I've added an 'Autofilter', with this you can filter on certain values in the column (click on the arrow and it will get clear).

Success,

Erik

[GayleK]

Erik,
THANK YOU!
I see it! It works! I am a visual learner so seeing it done in the steps(columns) certainly helps. Thank you.
If anyone has any additional comments/suggestions that would help me on the worksheet, feel free to add.
I do appreciate everyone's help. I learned something from each of you! Wow, this forum is great! I plan to come here and read, read read. Maybe one day I will be able to return the favor to someone else who is trying to learn!
Thanks!
Gayle

[martindwilson]

also a slightly modified sheet showing end date
this sheet works on that it knows what day it is when opened so will change week to week with no extra input
just add people as required (over write existing one would be best) just enter name start date

[WinteE]

It's good to hear you like to learn about Excel Gayle !!

The next peace of code will reset the autofilter on column G (Field:=7) every time the sheet is activated.
Copy the code to the VBA object of the worksheet where the autofilter is in.

You can do this as follows :

- Press <Alt>+<F11>, now you're in the VB editor
- Dubble-click 'Sheet1(Sheet1)' below the "Microsoft Excel -objects"
- Copy the code in the object

Please Login or Register  to view this content.

Use the macro-recorder if you want to learn more about VBA, ones you turn it on it will write code for every action you do in the Workbook. Don't forget to stop the recording before you're gonna look at the code.

Success,

Erik

[GayleK]

You all are awesome! Thanks so much!
I didn't have time to work with these right now but will after lunch! Eric, I must admit I had a little attack when I read "VBA". I have seen that on the forum in other places and kept wondering what it was. In reading and searching, I saw that it is coding and said I'm not ready for that yet! That is scarey to me in a way but I am going to get comfortable with it by learning about it---and using it!

I will have fun this afternoon! Thanks to you all! It is wonderful!