+ Reply to Thread
Results 1 to 9 of 9

Beginner help with data linking between sheets - VBA Required?

  1. #1
    Registered User
    Join Date
    02-12-2017
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    4

    Beginner help with data linking between sheets - VBA Required?

    Hi! I'm a first time user and short term listener...

    I'm hoping someone here might be able to assist me with my excel difficulties by providing some clear suggestions/code that is able to be understood by a beginner (assume i'm stupid and need step-by-step instructions please!).

    PROBLEM:
    I am trying to create a workbook to be used as a means of quickly listing on one sheet what tasks need to be completed for maintenance, on a Daily, Weekly, Fortnightly, Monthly, Quarterly and Annual basis.

    I have thought to use the first sheet as a 'summary' page which lists the task under headings such as "Daily" etc. with the addition of a 'check box' to be used when a task is completed.

    I have used additional sheets to list the tasks (every second column starting at column B) to be completed with each sheet being used separately for "Daily", "Weekly" etc. tasks in addition to a date of the year in column A and, a "Completed?" column located after each task, which I hope to have automatically updated from the check boxes on the Summary page.

    Each day, the Summary page should refresh, check the TODAY date, find the TODAY date in a given task sheet and, if the tasks haven't been marked as "Completed?", import the tasks for that date located in the following columns.

    Additionally, the Summary page should check all previous dates for any non-"Completed?" tasks and, import them under a separate heading on the Summary page titled "Overdue".

    Once a task has been marked as "Completed?", the task should be removed from the Summary page.

    Summary Page
    Summary.png

    Daily Tasks Page
    Daily Tasks Page.png

    Weekly Tasks Page
    Weekly Tasks Page.png


    Is anyone able to assist me with this and, provide suggestions on how i can make this work?

    Any assistance would be greatly appreciated!
    Last edited by AliGW; 02-17-2017 at 10:14 AM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,607

    Re: Beginner help with data linking between sheets

    Well, you obviously have an Excel file, so why don't you attach that rather than pictures of it? We can't try out different formulae in picture files, and most contributors would be reluctant to type out all your data before being able to try different things in Excel.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    02-12-2017
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    4

    Re: Beginner help with data linking between sheets

    This is the excel file with what I've developed so far. It's far from complete and missing a lot of information, yet I'm hoping it will be sufficient as a starting point.

    Many thanks for your assistance!
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,519

    Re: Beginner help with data linking between sheets

    This should get you started:

    A5 =IFNA(INDEX(Daily!$A$5:$Z$386,MATCH(Summary!$E$2,Daily!$A$5:$A$386,0),COUNTA($A$4:$A4)*2),"")
    B5 =IFNA(INDEX(Daily!$A$5:$Z$386,MATCH(Summary!$E$2,Daily!$A$5:$A$386,0),(COUNTA($A$4:$A4)*2)+1),"")

    Copy down.

    If you need help understanding the formula so that you can adapt it for other sections of the summary sheet, just shout. Please don't expect anyone to do the whole lot for you, though - I am concerned that this is what you are expecting from the detail in your opening post.
    Last edited by AliGW; 02-17-2017 at 04:38 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,519

    Re: Beginner help with data linking between sheets

    By the way, I really don't think your workbook structure is particularly good or helpful, either in terms of populating your summary sheet or from a user's point of view. Here's how I would do it:

    Excel 2016 (Windows) 32 bit
    L
    M
    N
    O
    3
    DATE
    Task
    Type
    Comp?
    4
    5
    Thursday, 15 December 2016
    Clean Dog Bowl
    Daily
    6
    Thursday, 15 December 2016
    Feed Dogs
    Daily
    7
    Thursday, 15 December 2016
    Dishes
    Daily
    8
    Thursday, 15 December 2016
    Check Mail
    Daily
    9
    Thursday, 15 December 2016
    Weekly Shopping
    Weekly
    10
    Thursday, 15 December 2016
    Dog Training
    Weekly
    11
    Friday, 16 December 2016
    Clean Dog Bowl
    Daily
    12
    Friday, 16 December 2016
    Feed Dogs
    Daily
    13
    Friday, 16 December 2016
    Dishes
    Daily
    14
    Friday, 16 December 2016
    Check Mail
    Daily
    Sheet: Daily

    There are two big advantages to keeping all of the raw data in this format on one tab:

    1. It can easily be filtered to daily, weekly, etc. as required by the user when ticking off tasks.
    2. It makes the extraction of data to the summary tab much easier in terms of formulae required.

  6. #6
    Registered User
    Join Date
    02-12-2017
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    4

    Re: Beginner help with data linking between sheets

    Quote Originally Posted by AliGW View Post
    This should get you started:

    A5 =IFNA(INDEX(Daily!$A$5:$Z$386,MATCH(Summary!$E$2,Daily!$A$5:$A$386,0),COUNTA($A$4:$A4)*2),"")
    B5 =IFNA(INDEX(Daily!$A$5:$Z$386,MATCH(Summary!$E$2,Daily!$A$5:$A$386,0),(COUNTA($A$4:$A4)*2)+1),"")

    Copy down.

    If you need help understanding the formula so that you can adapt it for other sections of the summary sheet, just shout. Please don't expect anyone to do the whole lot for you, though - I am concerned that this is what you are expecting from the detail in your opening post.

    Many thanks for your assistance AliGW!

    I've copied and pasted as directed.

    A pop-up displays to open a file with the heading "Update Values: Summary". I assume I need to then open up the same file for the update to the "Summary" sheet to come from the "Daily" sheet? If correct, I've done so, selecting the "Daily" sheet in the option provided, which results in "#NAME?". Am I missing something?

    To use the formula for other sheets instead of "Daily", I assume I need to substitute the other sheet names and correct changes to column references similar to:

    =IFNA(INDEX(Weekly!$B$5:$Z$386,MATCH(Summary!$E$2,Weekly!$B$5:$B$386,0),(COUNTA($B$4:$B4)*2)+1),"")

    My understanding of the formula is limited so I'd always appreciate further clarification yet, from what I understand, the formula checks cells whether they have data in them or not, then searches a sheet for the date to match the TODAY date on the Summary page, counts how many empty cells there are and returns a value for the cell reference of the cells which are empty.

    While I appreciate all the help I can get, I'm certainly not interested in someone doing all the work for me - all I need is for someone to show/explain to me how to do it for myself. I'd rather learn and understand how to do it and, why it works, rather than simply copy and pasting what someone else has done with no idea about how to do it for myself in the future.

    Quote Originally Posted by AliGW View Post
    By the way, I really don't think your workbook structure is particularly good or helpful, either in terms of populating your summary sheet or from a user's point of view.
    The user input to tick off tasks as completed should, ideally, only be done through the "Summary" page with no need for a user to move to any other tab - i.e. one tab 'shopping' for all of their needs. Given that, while I'm not necessarily adverse to incorporating all the data into one sheet, I thought it might be easier to segregate it into relevant sheets for ease of 'back-end' use.

    Any further assistance you can provide would be appreciated!

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,519

    Re: Beginner help with data linking between sheets

    The user input to tick off tasks as completed should, ideally, only be done through the "Summary" page with no need for a user to move to any other tab - i.e. one tab 'shopping' for all of their needs. Given that, while I'm not necessarily adverse to incorporating all the data into one sheet, I thought it might be easier to segregate it into relevant sheets for ease of 'back-end' use.
    That's a completely different kettle of fish, then, and makes it all MUCH more complicated. I think you'd need VBA for that, and that's beyond my capabilities - sorry!

    I'm off out now - will look in again later.

  8. #8
    Registered User
    Join Date
    02-12-2017
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    4

    Re: Beginner help with data linking between sheets

    Quote Originally Posted by AliGW View Post
    That's a completely different kettle of fish, then, and makes it all MUCH more complicated. I think you'd need VBA for that, and that's beyond my capabilities - sorry!

    I'm off out now - will look in again later.
    There's no need to apologise! I appreciate all help and assistance!

    Quote Originally Posted by Phreaky View Post
    I have thought to use the first sheet as a 'summary' page which lists the task under headings such as "Daily" etc. with the addition of a 'check box' to be used when a task is completed.
    Perhaps I should clarify this further...?

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,519

    Re: Beginner help with data linking between sheets

    Yes, I think some more clarity might help. It looks like another mod has moved your thread to the VBA section, anyway, so hopefully someone with the expertise will be able to assist.

+ 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. linking data between to sheets
    By asqtexture in forum Excel General
    Replies: 7
    Last Post: 05-23-2016, 05:49 AM
  2. linking data on different sheets and on a graph
    By clovermarie85 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 09-04-2014, 04:29 AM
  3. Linking Data Between Sheets
    By Miki Lu in forum Excel General
    Replies: 4
    Last Post: 05-25-2013, 07:12 PM
  4. Linking data between sheets as it is added
    By nyeb44 in forum Excel General
    Replies: 5
    Last Post: 12-20-2012, 09:39 PM
  5. Linking data from 2 sheets
    By josh-lucy in forum Excel General
    Replies: 1
    Last Post: 11-26-2011, 06:22 PM
  6. Linking data to multiple sheets
    By dklatt in forum Excel General
    Replies: 2
    Last Post: 04-24-2010, 12:25 PM
  7. linking data from multiple sheets
    By sjhonny in forum Excel General
    Replies: 3
    Last Post: 01-08-2007, 01:10 PM

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