+ Reply to Thread
Results 1 to 10 of 10

Summary data by date from other tabs

  1. #1
    Registered User
    Join Date
    12-07-2011
    Location
    Guildford
    MS-Off Ver
    Excel 2016
    Posts
    44

    Summary data by date from other tabs

    Hello
    Not sure how else to describe this one.

    I have a summary tab with project names already in.
    I have those projects in their own separate tabs.
    The summary tab has a Actuals column, Forecast column and a Delta column.
    In each project tab, is has a total row for each of the above column headings ie: Actuals, Forecast and Delta BY MONTH. So I have 3 times 12 totals (January has columns for actuals, forecast and delta)

    What I would like to do is in the summary tab, for each project get the figure from the project tabs for each actuals and forecast, depending on the month I choose in the summary tab.
    ie: In the summary tab I choose, January, I want to show for all projects, what the Actual total is, as well as forecast and Delta.

    Currently I have the following:
    =IF(ISNA(VLOOKUP($B$1,INDIRECT("'" &$C7 &"'!$a$1:$BZ$10000"),K$1,FALSE)),0,VLOOKUP($B$1,INDIRECT("'" & $C7 & "'!$a$1:$BZ$10000"),K$1,FALSE))

    Where B1 is the lookup row value in each project tab (TOTALS)
    C7 is the Project name in the Summary sheet
    k1 relates to column numbers, I have number all columns in the summary and project tabs.

    Any thoughts?

    Thank you in advance

  2. #2
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Summary data by date from other tabs

    Please provide the sample excel for better understandings & to provide you better solution as well.
    Regards,
    Thangavel D

    Appreciate the help? CLICK *

  3. #3
    Registered User
    Join Date
    12-07-2011
    Location
    Guildford
    MS-Off Ver
    Excel 2016
    Posts
    44

    Re: Summary data by date from other tabs

    Hi
    As I am on a work computer, I seem unable to attach a file here. Will have to try when home

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,408

    Re: Summary data by date from other tabs

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Ali


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

    Forum Rules (updated August 2023): please read them here.

  5. #5
    Registered User
    Join Date
    12-07-2011
    Location
    Guildford
    MS-Off Ver
    Excel 2016
    Posts
    44

    Post Re: Summary data by date from other tabs

    Done, not seeing it on here tho
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-07-2011
    Location
    Guildford
    MS-Off Ver
    Excel 2016
    Posts
    44

    Re: Summary data by date from other tabs

    bump anyone have any advice?

  7. #7
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Summary data by date from other tabs

    I've tried to get the total by removing the merged cells from Project1, pls refer the attached file for more details
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-07-2011
    Location
    Guildford
    MS-Off Ver
    Excel 2016
    Posts
    44

    Re: Summary data by date from other tabs

    Many thanks!
    I think that's almost there, from the formula you have used:
    =INDEX('Project 1'!C8:K8,MATCH(1,(Summary!A1='Project 1'!C1:K1)*(Summary!D3='Project 1'!C2:K2),0))

    The first bit for c8:k8, may not be flexible enough. As people will add in additional rows into the project 1 tab, it needs to pick up the (Total £) row always rather than a specific row ie: c8:k8

    For example, if one were to add an additional line in project 1, then c8:k8 would no longer show 9, but something else, it wouldn't be the total.

  9. #9
    Registered User
    Join Date
    12-07-2011
    Location
    Guildford
    MS-Off Ver
    Excel 2016
    Posts
    44

    Re: Summary data by date from other tabs

    I'm also thinking of how to get a total sum based on:

    3 columns each showing actual forecast and delta. These 3 come under a month ie: January.
    So I now have 3 x 12 columns = 36

    In my totals I was to show, that based on what month I choose in a drop down box, I would like to see what the month forecasts are.
    ie: If I choose March, I should see just March forecast.

    Ideally, then what I would want is a running total of the month I choose and prior months sum together. so for example, if I choose march, to add Jan, feb and marc together.


    thanks!

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

    Re: Summary data by date from other tabs

    I am proposing a pivot table solution as I think it will give you flexibility in analyzing the data. It will require a restructuring of the data as shown in the Project 1 sheet, however all I really did was to copy and paste values transpose (Ctrl + Alt + v then choose values and transpose) the project 1 data immediately followed by the project 2 data. I then made a table so Excel will recognize when the data is appended. Make sure column headers are unique (I added 'Project', 'Month' and 'Details'). Sort by month (custom) as I imagine that new data will be added month by month (actually the pivot table won't care if the data is in order or not).
    In the pivot table 'Projects' are row labels and 'Details' are column headers. 'Totals' are placed in the values field and a 'Months' are placed in the slicer (you may choose any, a combination or all).
    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.

+ 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. Grabbing data from various tabs to highlight in a summary
    By przeziom83 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-14-2018, 06:08 AM
  2. [SOLVED] Summary Report from Multiple Data Tabs
    By arasan25 in forum Excel General
    Replies: 6
    Last Post: 08-20-2017, 06:43 PM
  3. Pulling Data from Multiple Tabs onto Summary Tab
    By tho12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2016, 09:27 AM
  4. [SOLVED] Extract Data from multiple tabs in one workbook to a summary tab
    By iceplant in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-24-2014, 12:36 PM
  5. [SOLVED] Summing in one summary tab data from multiple tabs
    By loulite in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-15-2014, 10:31 AM
  6. [SOLVED] Extracting data from summary tab to different tabs based on criteria
    By masond3 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 09-02-2013, 05:47 AM
  7. Combine data multiple tabs into one Summary Tab
    By rorybecers in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-12-2011, 05:59 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