+ Reply to Thread
Results 1 to 16 of 16

An easier way to connect formulas between tabs (complicated)

  1. #1
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    An easier way to connect formulas between tabs (complicated)

    Hi guys,

    Was working on this issue for last 6 months, not being able to crack the code... To manage this file, the way it's set up, it basically takes 1 day a month to complete updates, which, it feels, is a day of life wasted every month. Wondering if your smart brain would be able to suggest a better solution?

    I am attaching a file here:

    https://www.excelforum.com/attachmen...1&d=1506769829 [EXCEL]

    An issue in details:

    FILE PURPOSE:
    1. Monthly tabs = entire system, with categories and tasks + end of month report
    2. Daily tabs = each task is broken down into particular time of a day

    ACTION PROCESS:
    1. At end of each day go into a Daily tab (on Mon go to Mon tab, On Tue go to Tue tab, etc) , and state 'y' or 'no' to mark if a task was completed
    2. Review Monthly tab at the end of a month, to see how you performed in a particular category, and a performance of that particular month

    CHALLENGE:
    Each cell in a Monthly tab needs to be manually connected to cells in Daily tabs (please view a file attached, to see how its done). This process currently takes tremendous amount of work, every single month!

    WORK-AROUND?
    Based on details provided in a file, would it not be possible to set it in some way so that Monthly tab generates results based on a:

    1. month selected (cell AF1 in Monthly tab)
    2. day of the week that a month starts (row 3 on Monthly tab)

    I don't know if I'm making it clear, or what other details to provide. But if you take a look at a file, and witness the amount of work it currently takes to update each newly added Monthly tab, you will see exactly what is described here.

    Any suggestion for a better workaround this issue, would be greatly appreciated! Thanking you...
    Attached Files Attached Files
    Last edited by vill; 09-30-2017 at 10:59 AM. Reason: title change

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Smart Excel brain to save 1 day of life

    changed only January!D6:D11
    did not make "o", it is not in calculation
    =INDEX(INDIRECT(D$2&"B5:BB100"),MATCH($B6,INDIRECT(D$2&"B5:B100"),),MATCH($AF$1,INDIRECT(D$2&"B2:BB2"),)+MATCH(D$2,$D$2:$H$2,)-1)
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    An easier way to connect formulas between tabs (complicated)

    tim201110, you opened my eyes, so its possible! Wow, would be like dream come true...

    I'm trying to understand how does the formula work, and how to apply it for Feb tab as well. Cant seem to figure...

    I see that currently in Jan tab, if I change to Feb, it takes values for Feb as well, but only from Mon! tab, and not from any others?

    Kindly, would it be ok to ask to apply same formula to FEBRUARY tab as well (already included in original file copy) ? I will try to figure everything else, if I will...

    Thank you, so much. This is truly 'wow'
    Last edited by vill; 09-30-2017 at 11:00 AM.

  4. #4
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Smart Excel brain to save 1 day of life

    just input workday sheet names in row 2 in month sheets
    and it should work for the whole year
    from 38:30 a minute
    https://www.youtube.com/watch?v=b3segTP3QTg&t=2060s
    Last edited by tim201110; 09-30-2017 at 10:13 AM.

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Smart Excel brain to save 1 day of life

    Administrative Note:
    • We would love to continue to help you with your query, but first, before we can proceed…
    • Please see Forum Rule #1 about proper thread titles and adjust accordingly...
    HTH
    Regards, Jeff

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Smart Excel brain to save 1 day of life

    In "January (2)" C6

    =INDEX(INDIRECT("'" &D$3&"'!C5:Q100"),MATCH($B6,INDIRECT("'" &D$3&"'!B5:B100"),0),MATCH($AF$1,INDIRECT("'" &D$3&"'!C2:Q2"),0))

    Copy across for Mon-_Fri (week 1)and copy repeat for other weeks with this formula

    =INDEX(INDIRECT("'" &K$3&"'!C5:Q100"),MATCH($B6,INDIRECT("'" &K$3&"'!B5:B100"),0),MATCH($AF$1,INDIRECT("'" &K$3&"'!C2:Q2"),0)+1)

    Week3 will be +2, Week 4 wll be +3, Week 5 will be +4

    I also changed day to match sheet names (Mon","TUE" etc)

    See sheet "January (2)"

    Do similar changes for "February"
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: Smart Excel brain to save 1 day of life

    .. must have missed (no refresh) request to change title before replying to post.: apologies

  8. #8
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Smart Excel brain to save 1 day of life

    May I remind all contributors of Jeff's post #5

    As well as ignoring that request you're now ignoring rule # 7a as well.

    This thread should receive no further responses until the title has been changed in line with the rules.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  9. #9
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    An easier way to connect formulas between tabs (complicated)

    Sorry, was not aware of title rules. Changing it now

  10. #10
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Re: An easier way to connect formulas between tabs (complicated)

    Ok, so I managed to understand how both examples, by tim201110 and JohnTopley, work. Amazing job, guys, so grateful.

    I found that example tim201110 required me to make changes to each cell by updating -1 to -2, +1 etc, and it ended up working.

    The example given by JohnTopley require no tweaks, and feels simpler approach. I do however bump into couple issues here...

    @JohnTopley:

    1. is there a way to remove those large 0 when cells in Daily tabs are empty, and replace them with "o" or "-" ?
    2. I just realized, there is no way I'm gonna be able to figure how to apply same formula to Weekend tab, where Saturday and Sunday are in a same tab

    I have cleaned up some data from John's example, to make it simpler, but added Weekend tab. Attaching:

    https://www.excelforum.com/attachmen...1&d=1506791035 (EXCEL}

    Is there a way I could please ask to help make those adjustments, as per 2 points mentioned above?

    If however, there is no way to make Weekend tab work without splitting Sat and Sun into tabs, really not the best way, but perhaps could use this option, as alternative. But please suggest.

    Still cant believe you guys manage to crack it up just like that... weeks wasted on this task, over last months! Thank you so much...
    Attached Files Attached Files

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: An easier way to connect formulas between tabs (complicated)

    For 1.

    CUSTOM format as

    0;-0;-;@


    I'll look at 2. tomorrow

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: An easier way to connect formulas between tabs (complicated)

    For 1st Saurday (in any month)

    =IFERROR(INDEX(Wknd!$C$5:$Q$7,MATCH($B6,Wknd!$B$5:$B$7,0),MATCH($AF$1,Wknd!$C$2:$Q$2,0)),"-")

    For 1st Sunday

    =IFERROR(INDEX(Wknd!$C$9:$Q$11,MATCH($B6,Wknd!$B$9:$B$11,0),MATCH($AF$1,Wknd!$C$2:$Q$2,0)),"-")

    For next weekend (Saturday) ...

    =IFERROR(INDEX(Wknd!$C$5:$Q$7,MATCH($B6,Wknd!$B$5:$B$7,0),MATCH($AF$1,Wknd!$C$2:$Q$2,0)+1),"-")

    then add 2,3,4 for other weekends

  13. #13
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Re: An easier way to connect formulas between tabs (complicated)

    John, gosh, you're the best! Thank you so much.

    I was actually just about to hit another message, as managed to figure how to sort it with Wnkd tab. But here, bumped into another issue right away...

    Attaching a file with details explained. But basically, I came to learn that now since there are different formulas for different tabs, every time a month is changed (from Jan to Feb etc) I'll be bumping into manual updates again...

    Attaching a file again, clean simple version. Wondering, is there anything could be done to eliminate manual work completely, whenever new month is changed?

    Anytime, if not too much to ask!

    PS: formating tip helped also, thank you!!

    https://www.excelforum.com/attachmen...1&d=1506804307
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Re: An easier way to connect formulas between tabs (complicated)

    A correction! Days showing in C3:AG3, I meant, and NOT C4:AG4 as stated in excel file

    And just to summarize - every single cell in JANUARY tab should contain a formula, but bring in values only when:

    1. it matches a day in C3:AG3 (Mon-Fri and Sat, Sun formulas are different)
    2. it matches an existing task in Daily tab with a task in Monthly tab
    3. if both criterias are met, but cell in Daily tab is empty, show 0 (or "-" after formating)
    4. if criterias are not met, show blank

    A goal, if this is possible to achieve, would be to eliminate any manual work when a month changes and mixes weekdays around.

    I hope that explains...

    I tried mixing those formulas myself, but getting nowhere with it all....

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,005

    Re: An easier way to connect formulas between tabs (complicated)

    I don't see any difference in the new file from any previous file.

    You are always going to have a problem when converting a weekly record (Mon-Fri, Wknd) to the Month daily record. If you want to use the dates in the Month summaries it might be better to replace the "Wk1", "Wk2" etc entries with a week beginning date which then gives the potential to a date/date comparison.

    "missing" tasks can be addressed using the IFERROR formula used in the "Wknd" formulae


    I also note that January in the daily sheets has #REF errors.

    A much better solution is simply to use the Monthly sheets as data entry and dispose of the Daily sheets. Simply transposing the data adds no [obvious] value and is costly in time!

    If you "inverted" the table with tasks as column headings and dates as rows you could collect all your data on one sheet and if required produce monthly summaries.
    Last edited by JohnTopley; 10-01-2017 at 03:23 AM.

  16. #16
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Re: An easier way to connect formulas between tabs (complicated)

    Hi John,

    First copy had 14 tasks, and looked little cluttered. Last copy I kept only 4 tasks.

    Taking everything into consideration, I decided to keep your original formulas. I've just completed October tab, took me about 30 mins, which is a blessing, as it used to be 1 full day... I cant tell in words how grateful I am for your help... you truly saved the world here! I no longer dread end of month to-do list :-)

    Thank you so much!!! A lot.

    Wishing you a great day,
    - William

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Silly Excel Formula Question (having a brain freeze moment)
    By rjw524 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-05-2015, 02:46 PM
  2. Replies: 9
    Last Post: 01-25-2014, 02:00 AM
  3. EXCEL for Life Sciences
    By ManRai in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-22-2014, 01:51 AM
  4. help me save hours of my life
    By iggyfalk in forum Excel General
    Replies: 2
    Last Post: 02-13-2008, 07:44 AM
  5. Excel Life
    By mikerickson in forum Excel General
    Replies: 0
    Last Post: 05-15-2007, 04:52 PM
  6. Replies: 3
    Last Post: 03-22-2007, 05:06 PM
  7. Replies: 1
    Last Post: 05-20-2005, 09:06 AM

Tags for this Thread

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