+ Reply to Thread
Results 1 to 11 of 11

Functions to link data over multiple sheets.

  1. #1
    Registered User
    Join Date
    05-28-2020
    Location
    Perth, Australia
    MS-Off Ver
    MS Office 2016
    Posts
    5

    Post Functions to link data over multiple sheets.

    Hi Everyone,

    I'm new to all the Excel functions but have self taught everything I know just through reading and researching.

    I've hit a problem that I've been having a crack at for 2-3 days now.

    I have a stocklist and 12 tabs labelled January - December where we report the stock as used.
    All Data is in drop down lists in the months in column L and the stock used is in column N.
    I can get January to show up the in the stocklist using this function here:

    =SUMIFS(January!N:N,January!L:L,"Name of Stocked Item")

    I don't know how to add the other 11 months without an error coming up or if i'm even using the right function, weather I should be using COUNTIFS instead.

    Any help would be massively appreciated.

    Thank you.
    Attached Files Attached Files
    Last edited by Kanga Azz; 06-03-2020 at 07:28 AM.

  2. #2
    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,368

    Re: Functions to link data over multiple sheets.

    Welcome to the forum.

    There are instructions at the top of the page explaining how to attach your sample workbook.
    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.

  3. #3
    Registered User
    Join Date
    05-28-2020
    Location
    Perth, Australia
    MS-Off Ver
    MS Office 2016
    Posts
    5

    Re: Functions to link data over multiple sheets.

    I have updated with an example spreadsheet of what I need done.

    Need the Items Used and Amount in January - March Columns L and N to reflected in the stocklist columns A and B

  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,368

    Re: Functions to link data over multiple sheets.

    What you need DONE? This is a help forum, so the assumption is that you have tried something already, but can't get it to work.

  5. #5
    Registered User
    Join Date
    05-28-2020
    Location
    Perth, Australia
    MS-Off Ver
    MS Office 2016
    Posts
    5

    Re: Functions to link data over multiple sheets.

    Sorry, Didn't mean to sound demanding. Poor choice of words, I do apologize.

    I have tried alot of different variations over the last few days. managed to get January to work with this one:

    =SUMIFS(January!N:N,January!L:L,"Data 1")

    But couldn't get the other months into the function.

    Again, I apologize if I came across as rude, that was not my intention.

  6. #6
    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,368

    Re: Functions to link data over multiple sheets.

    Not rude, just as if you didn't quite get how the forums work.

    I'll have a look for you.

  7. #7
    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,368

    Re: Functions to link data over multiple sheets.

    OK - I've had a look and there is no data in the workbook that can be summed.

    A sample workbook needs to contain (a) representative, desensitised data and (b) manual mock-ups of the results you want. 10-15 rows of mock data and results is usually enough to demonstrate what you want.

    When you provide a more useful sample file, I'll take another look.

  8. #8
    Registered User
    Join Date
    05-28-2020
    Location
    Perth, Australia
    MS-Off Ver
    MS Office 2016
    Posts
    5

    Re: Functions to link data over multiple sheets.

    Sorry I've been a bit painful on here lol.

    I have made up some data and input the formula into the Stocklist 'Data B' Column.

    January seems to work as shown (I've only put in Data 1-4 in there and Data 5-10 in February and March), but can't get the other months to add in.
    I've tried variations similar to the function in Stocklist B3 position. Wasn't sure if it was the right formula to be using.

    I hope this is what is needed in the mock sheet. First time doing something like this.

    Thank you.
    Attached Files Attached Files

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

    Re: Functions to link data over multiple sheets.

    About the easiest way to accomplish this is to do a summation on each of the monthly sheets and then reference the summation using the SUM function.
    The summations are in S2:S11 on each sheet and use the formula: =SUMIFS(N$2:N$13,L$2:L$13,R2)
    I would suggest converting the ranges A1:P13 on each monthly sheet into tables.
    On the sheet the column is populated using: =SUM(January:List!S2)
    The reason that List is used is that I assume it will always be the last sheet in the workbook.
    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.

  10. #10
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Functions to link data over multiple sheets.

    Just another option
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    05-28-2020
    Location
    Perth, Australia
    MS-Off Ver
    MS Office 2016
    Posts
    5

    Re: Functions to link data over multiple sheets.

    Cheers for the input guys, Unfortunately it wasn't what i needed as I have about 200 items in 4 different categories to input.

    I did manage to get it to work after 8 days of trial and error.

    I can put get all 12 months to SUM together by means of a dropdown list using this function:

    =SUMIF(January!$L:$L,"Data1",January!$N:$N)+SUMIF(February!$L:$L,"Data1",February!$N:$N)+SUMIF(March!$L:$L,"Data1",March!$N:$N)+SUMIF(April!$L:$L,"Data1",April!$N:$N)+SUMIF(May!$L:$L,"Data1",May!$N:$N)+SUMIF(June!$L:$L,"Data1",June!$N:$N)+SUMIF(July!$L:$L,"Data1",July!$N:$N)+SUMIF(August!$L:$L,"Data1",August!$N:$N)+SUMIF(September!$L:$L,"Data1",September!$N:$N)+SUMIF(October!$L:$L,"Data1",October!$N:$N)+SUMIF(November!$L:$L,"Data1",November!$N:$N)+SUMIF(December!$L:$L,"Data1",December!$N:$N)

    I mean, if anyone knows how to shorten that, feel free to massage haha.

    But thank you for your help.

+ 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. Replies: 7
    Last Post: 02-08-2017, 08:10 PM
  2. Link data to an ID across multiple sheets
    By Rsnead91 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-20-2014, 11:28 PM
  3. Link and transpose data from multiple sheets into a summary
    By ddalluge in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-13-2013, 09:03 AM
  4. [SOLVED] How to create multiple sheets and link data
    By JOHNAK27 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-02-2013, 04:03 AM
  5. Help with linking data validation list and functions on multiple sheets.
    By cmcglli in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-31-2013, 09:34 PM
  6. Replies: 2
    Last Post: 02-03-2012, 02:58 AM
  7. Link data from multiple data sheets
    By gsweta in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-06-2009, 02:50 AM

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