+ Reply to Thread
Results 1 to 10 of 10

Linking Tabs in a worksheet, matching topics with dates

  1. #1
    Registered User
    Join Date
    04-28-2013
    Location
    U.S.
    MS-Off Ver
    Excel 2010
    Posts
    8

    Linking Tabs in a worksheet, matching topics with dates

    Hello, i'm a new member with just really basic knowledge about excel, and in the search of a few formulas to get these 2 worksheet working, hopefully you guys can help me out to finish them up, any advice will be appreciate it!!! thank you!

    In the first one "DRUM OPERATOR SAFETY" there are only 2 active tabs: 1.- 980 SAFETY CHECK LIST, 2.-SAFETY TOPICS; In tab #1 i have daily date in tab A7 and safety topic in B7; in tab #2 i have my safety topics that go by date. I'd like to have tab 1 and 2 linked up, if there's any formula to update date in tab #1 cell: A7 automatically in a dialy basis in this format: MM/DD/YEAR, and once updates date, i'd like to link date to the safety topics in tab #2, date runs from B3 to B300, and proper Safety Topic for each day runs from C3 to C300, my ending goal would be once date automatically updates in Tab #1 cell A7, will find safety topic within Tab #2 matching the date. DRUM OPERATORS SAFETY (version 1).xls

    In the second one "DRUM SCHEDULE", it's a workbook of 7 tabs that represent the 7 days of the week Monday to Sunday; on this one i created a custom list representing 31 days of the month (1,2,3,4,5,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z), i have been looking for a formula without success. What i would like to have is a formula in "Monday" tab cell D9 that once i put in my Custom list character in it, will take an increment effect on the remaining tabs in consecutive order mon, tue,wed,thu,fri,sat and sun; so if i type in "W" in D9 Monday's Tab, Tuesday D9 must be "X", wed D9 ="Y", thu D9 = "Z" and start custom list all over again fri D9 = "1" sat D9 = "2" , sun D9 = "3".DRUM SCHEDULE.xls

    Any help will be greatly appreciate it!! thank you guy for this excellent website!!

    Regards Yeritson.
    Last edited by yeritson; 04-29-2013 at 09:46 PM.

  2. #2
    Forum Contributor
    Join Date
    03-27-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    154

    Re: Please need help with a few formulas, thx!!

    Hi,

    Part one is attached.

    Part two with your custom list- do you want 31 days even when a month does not have 31 days? should it always cycle through the full 31 day cycle?

    if so, will the date on each sheet be updated manually and replaced each week? If so, will it always follow a regulated cycle, meaning that if you use May 01/13 as "A" in custom list, it will always be an "A" forever, and as such, all future custom list references are relative to it, meaning May 02/13 will be "B", May 03/13 will be "C" OR can you restart the naming cycle meaning May 01 can be A and you can make May 03 A as well?

  3. #3
    Registered User
    Join Date
    04-28-2013
    Location
    U.S.
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Please need help with a few formulas, thx!!

    It would be perfect if you can kindly make it, if once you type in month will recognize it and will auto convert it to 30 or for february 28, leap year?? wow, i don't know if that is asking too much thank you!

  4. #4
    Forum Contributor
    Join Date
    03-27-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    154

    Re: Please need help with a few formulas, thx!!

    Yeritson,

    Will day 1 always equal 1 and day 31 always equal Z? or will you be switching the codes mid month, meaning May 01 = 1, but May 6 you want to be "2" or "K" or any other sequence?

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Please need help with a few formulas, thx!!

    Hi and welcome to the forum

    We would love to help you with your question, but 1st, in accordance with forum rule, please rename your thread to something more meaningful, that actually describes your problem.

    Because thread titles are used in searching the forum it is vital they be written to accurately describe your thread content or overall objective using ONLY search friendly key words. That is, your title used as search terms would return relevant results.

    Also, many members will look at a thread title, and if it is of interest to them, or it falls within their area of expertese, they might only open those threads.

    Look at it this way...if you typed that title into google, what would you expect to get back?
    To change a Title on your post, click EDIT on you're 1st post, then Go Advanced and change your title
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Forum Contributor
    Join Date
    03-27-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    154

    Re: Please need help with a few formulas, thx!!

    Hey,

    I agree with FDibbins and encourage you to change the post title so that it may help others that may be looking for a solution to a similar problem. The way I would approach your second problem is by simply looking at the day of the month in the date field of each sheet, pull out the day of the month, so today April 29th would be 29, then look up what 29 translates to in your custom naming convention from another table, match the number, then return the corresponding custom name based on that date. You can use Excel's vlookup formula to achieve this. This is assuming that each first day of the month start the custom sequence over again.

    I have attached a sheet with a possible solution in the hopes that you will change the post title.

    Thanks.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-28-2013
    Location
    U.S.
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Linking Tabs in a worksheet, matching topics with dates

    Jarko28 thank you very much!first worksheet worked like a charm! on the second one (last) worked really good, but i forgot to explain you this: our production takes 48 hrs to get final product, so what we start producing today Tuesday the 30th of april on Tuesday tab D9 shows character of two days later of current day: "2" ; Tomorrow will be Wednesday 1st of may,so in Wednesday's Tab D9 should show "3" and so on until Fiday 31st of May, so in that friday tab D9="2", like the attached file: DRUM SCHEDULE-2.xls Thank you in Advance!!




    P.D.:Title changed! i'm sorry about the inconvenience
    Last edited by yeritson; 04-30-2013 at 11:08 PM.

  8. #8
    Forum Contributor
    Join Date
    03-27-2012
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    154

    Re: Linking Tabs in a worksheet, matching topics with dates

    Yeritson,

    Here you go. Just add +2 to the cell that references your date on each sheet and wrap it in the DAY function...I have attached the file. I did notice the Friday tab is different than the rest of the tabs, it is missing a column. I wasn't sure if this was intentional or not, but I adjusted the formula to look in cell J5 on that tab in the event it was intentionally different. If it wasn't and the reason is that each tab was manually recreated, I would suggest you delete the sheet and make a copy of the other tab to make it uniform so your dates are always in cell K5...

    Also, you may want to copy the formulas from the Monday sheet in the results section so that it does not give you errors when qty =0 or blanks, by wrapping the division formula in an iferror formula, you can have a cleaner looking sheet.

    Thanks.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    04-28-2013
    Location
    U.S.
    MS-Off Ver
    Excel 2010
    Posts
    8

    Re: Linking Tabs in a worksheet, matching topics with dates

    Jarko28:
    I really appreciate all your help and support, and indeed problem has been solved! Do you have any suggestions of any book to get this type of sheets solved? Thank you!!!

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Linking Tabs in a worksheet, matching topics with dates

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1