I am a relatively new user, who's trying to formulate a recurring calendar, with 15 users in succession, by month. I don't yet know how to write formulas, or anything else.I would appreciate any help. I'm enclosing a sample sheet. I would need to know how to incorporate each name every 15 days. I would greatly appreciate any and all helpful advice. Thank you!
Last edited by brenetedwards; 08-29-2021 at 03:52 PM. Reason: Added attachment
Nothing attached. Please read the yellow banner at the top of this page.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
I'm sorry, I thought I did! It should be there now! Thanks!
Hello brenetedwards. Welcome to the forum.
Try this in cell A5 and copy across G5. Once done copy the range A5:G5 and paste into A10, A16, A22 and A28.
See the attached. It is already done for you there. When you transition to the next month you may need to adjust this formula.Formula:
Please Login or Register to view this content.
Dave
Excellent! Thank you! Would you be able to get me a formula for recurring months? I'd really appreciate it!
I ran into a snag in October...I tried addending the original formula with the corresponding cell numbers for October, and I guess I did it wrong, because it copied the formula all the way across! Please bear in mind that I'm a newbie! I really appreciate your help!
When I went to post this to my actual calendar, nothing happened. I must be doing something wrong. I even added the data sheet to it...please advise! Thank you again!
When I went to post this to my actual calendar, nothing happened. I must be doing something wrong. I even added the data sheet to it...please advise! Thank you again!
Dave's formula works fine for September as it has 30 days, and it will continue to give the correct results into October. However, the same (first) name will pop up on 31st October and on 1st November, so it is the formula for November which will need a bit of tweaking.
Incidentally, you do not need to type every date into your calendar. Once you have typed 01/09/2021 into D4, then in E4 you can just use the formula:
=D4+1
and copy this to G4. In A9 you would have the formula:
=G5+1
and in B9:
=A9+1
which can be copied across to G9. Then you can copy the cells A9:G9 down into A15,A21 and so on. Your calendar also needs a blank row to be inserted in the top week, to make it consistent.
Hope this helps.
Pete
@ Pete
Thanks. Never thought about the adding "1" bit. I almost always overlook the simple ways.
Dave
The formula worked great in your model, but then when I went to put it into my calendar, nothing happened, and I haven't been able to figure it out. Do you type the whole original formula into each cell, or just the cell # +1?
I've attached the file where I have inserted a blank row within the first week block, as advised earlier, and put in the formulae for date-increments. I've extended this into November.
I've also used this formula in A5:
=IF(A4="","",INDEX(DATA!$B:$B,MOD(INT((ROWS($1:1)-1)/6)*7+COLUMNS($A:A)+COUNTIF($A$4:$G$4,"")-7,15)+1))
which is copied across to G5, and then that block of formulae is copied into each weekly block.
Hope this helps.
Pete
Thank you! I have a lot to learn, because I couldn't make it work...I'll keep trying!
Try this. Copy and paste as before.It starts the count at the first of the year. I had to "cheat" (subtract 3) to get Island Fellah to start on the first of September.Formula:Please Login or Register to view this content.
Thank you! I’ll try it tomorrow!
I don't know how to help with calendar format, but this might help spanning months, years, etc.
Key the beginning Date into A1. Key the names Names into B1:B15.
In A2 key formula =A1+1. In B16 key formula =INDIRECT(ADDRESS(ROW()-15,COLUMN())).
Copy A2 and Fill/Down as many rows as desired. I filled down through Row 110, which is enough to span several month changes, a year change and leap year Feb 29.
Select B16 and Fill/Down to the row filled with dates. Groups repeat regardless of the number of days in a month & into the next year.
Minor changes can accommodate larger or smaller groups.
I'm new here and have had problems uploading files. So I'm not even trying to do so here. Hopefully, it's not needed.
I began with Nov 22, 2019 and ended with Mar 10, 2020.
Last edited by jarhtmd; 09-12-2021 at 03:18 PM. Reason: added info
Since I'm new here, I have just been exploring a little to see the variety of the posts. I ran across a thread "Help with inputting data from one sheet into calendar" which might be useful to you. See post #2 by JohnTopley. Since I'm so new on the forum, I'm not yet allowed to post the link.
Maybe you could use the "vertical" data as I described in my earlier post in combination with suggestions in the above thread.
As I said, I've never used calendars in Excel, but to me the combination seems like it could be helpful. I hope so.
Are you stll using XL2007, as your profile states? If not, please update this by clicking on UserCP at the top of the screen.
Pete
Yes, still using XL2007. I know it’s retired. I am too. I don’t use XL much any more & there’s no reason for me to upgrade.Originally Posted by Pete_UK
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks