+ Reply to Thread
Results 1 to 8 of 8

Create a dashboard from multiple tabs

  1. #1
    Registered User
    Join Date
    11-15-2021
    Location
    Glasgow, Scotland
    MS-Off Ver
    365 MSO 32-BIT
    Posts
    4

    Create a dashboard from multiple tabs

    Morning All,

    This is my first post, so please bear with me if I have to ask multiple questions.
    I am looking to create a dashboard that updates with each cell entry (Live updates). I have a single spreadsheet from which to gather the data, but there will be 52 tabs (1 per week for a whole year), from which the data will be gathered
    I have assumed that to start this off it would be best to gather all the information I need and collate this onto a separate tab, then use this tab to create the dashboard.
    The trouble I have is that although I can count the number of drops done by each driver from a single spread sheet using the following formula =COUNTIF('Week 1'!E5:AA127, "allan") I cannot manage to then get additional data from the other tabs I have created.
    I then tried the following formula =SUMPRODUCT(COUNTIF(INDIRECT({"'WEEK1'!D5:AA127";"'WEEK2'!D5:AA127";"'WEEK3'!D5:AA127"}),A12)), but I cannot get this to work?

    I would like to show the quantity of drops done by each driver, and how many times we go to each area, both on a daily basis. There are other things I would like to show, but I would like to get the basics done first then I can work on the rest, as this would help me develop an understanding on how excel works?

    Any help to push me in the right direction, would be appreciated.
    Attached Files Attached Files

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Create a dashboard from multiple tabs

    Tricky with only a partial workbook to go at. Your data is arranged in a way that makes it difficult to do this and I suspect your dashboard could end up running quite slow. However, you might be able to use something like this:

    Please Login or Register  to view this content.
    That should look for the contents of A12 in all the sheets from WEEK1 to WEEK52. The problem is that INDIRECT is volatile so all formulas in this style will get recalculated when you change *anything* on *any* sheet which could really slow down your workbook.

    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Registered User
    Join Date
    11-15-2021
    Location
    Glasgow, Scotland
    MS-Off Ver
    365 MSO 32-BIT
    Posts
    4

    Re: Create a dashboard from multiple tabs

    Thanks for this WBD. Can I ask if there would be a better way to arrange the data, so it works quicker?

    T

  4. #4
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Create a dashboard from multiple tabs

    It's difficult because you have two issues:

    1. You want to be able to enter the data in an easy-to-use manner.
    2. You want to be able to report on the data that's been added.

    The two don't go hand-in-hand. I think if I was doing this, I'd end up using VBA to maintain an extra "Data" tab that records everything entered into the "WEEKxx" sheets and which I could then use for reporting. However, it's quite a big task and not for the faint hearted!

    WBD

  5. #5
    Registered User
    Join Date
    11-15-2021
    Location
    Glasgow, Scotland
    MS-Off Ver
    365 MSO 32-BIT
    Posts
    4

    Re: Create a dashboard from multiple tabs

    OK Thanks WBD.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Create a dashboard from multiple tabs

    Agree with Wide Boy Dixon that the arrangement of the data is not optimal. (see https://www.youtube.com/watch?v=JzjXY7GrjNw&t=3s between the 10:45 and 14:25 marks)
    That said you might try the following.
    1. Produce a distinct list of drivers which would need to be the same on each weekly sheet
    2. Count the frequency of a driver using: =SUMPRODUCT(--(TRIM(E$6:W$20)=TRIM(E23)))
    3. The formula for the overall count for davie could be: =SUM(Sheet1:Sheet52!F23)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    11-15-2021
    Location
    Glasgow, Scotland
    MS-Off Ver
    365 MSO 32-BIT
    Posts
    4

    Re: Create a dashboard from multiple tabs

    Thanks Jetemc, the formulas work well for what I need.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,425

    Re: Create a dashboard from multiple tabs

    You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

+ 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. How to create a summary table from multiple different tabs?
    By Leevi90 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 09-21-2017, 03:21 PM
  2. Replies: 1
    Last Post: 03-17-2014, 10:23 AM
  3. create multiple work books with multiple tabs
    By jlsbexcel in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-05-2013, 09:05 AM
  4. How to create and edit multiple tabs simultaneously
    By brandnew22 in forum Excel General
    Replies: 8
    Last Post: 08-14-2013, 10:54 AM
  5. IS there a way to create multiple tabs WITHIN a spreadsheet?
    By ooocharmsooo in forum Excel General
    Replies: 1
    Last Post: 12-28-2011, 12:17 PM
  6. Replies: 1
    Last Post: 07-19-2011, 10:22 PM
  7. [SOLVED] Create multiple sheet tabs from multiple cells.
    By Robert Maddox in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 07-19-2006, 05:40 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