+ Reply to Thread
Results 1 to 5 of 5

Formula that takes data from changing worksheets

  1. 06-02-2016, 03:58 AM #1
    Dlinen
    Dlinen is offline
    Registered User
    Join Date
    05-27-2016
    Location
    Zurich
    MS-Off Ver
    Office 2016
    Posts
    61

    Question Formula that takes data from changing worksheets

    Hi Guys,

    I have a sheet where I am running monthly analyses. Now I would like to have one graph updated so that it always shows the latest month which is in the last worksheet. Now I'm trying to put together a formula that takes data from changing worksheets. I have two strategies.

    Strategy 1: Ideally I find a formula that automatically replaces the 'Mai 2016' (in this formula: =SUMIF('Mai 2016'!$F$2:$F$159;I6;'Mai 2016'!$B$2:$B$159) ) with the same data from the last worksheet (which I will update monthly)

    Strategy 2: Should that not work i would like to try to have a drop down where I can choose what sheet the formula takes the data from. The Sheet's are all named Jan 2016, Feb 2016, etc. until Dec 2016.

    Can anyone help me with this?

    Thanks in advance!
    Reply With QuoteRegister To Reply
  2. 06-02-2016, 04:04 AM #2
    AliGW
    AliGW is online now
    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
    80,852

    Re: Formula that takes data from changing worksheets

    Use the second strategy, then the INDIRECT function to reference that cell with the sheet name in it.

    Have a look at this great resource on the INDIRECT function: http://www.contextures.com/xlFunctions05.html
    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.
    Reply With QuoteRegister To Reply
  3. 06-02-2016, 04:07 AM #3
    JohnTopley
    JohnTopley is online now
    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,205

    Re: Formula that takes data from changing worksheets

    Try

    =SUMIF(INDIRECT("'" & B1&"'!$F$2:$F$159"),I6,INDIRECT("'" & B1&"'!$B$2:$B$159"))

    B1= Tab name ("Main 2016") which could be your drop down
    Reply With QuoteRegister To Reply
  4. 06-02-2016, 04:08 AM #4
    shukla.ankur281190
    shukla.ankur281190 is online now
    Forum Expert shukla.ankur281190's Avatar
    Join Date
    05-17-2014
    Location
    Lucknow, India
    MS-Off Ver
    Office 365
    Posts
    3,936

    Re: Formula that takes data from changing worksheets

    Try

    =SUMIF(INDIRECT("'"&A1&"'"&"!"&"$F$2:$F$159"),I6,INDIRECT("'"&A1&"'"&"!"&"$B$2:$B$159"))

    A1 will be your sheet reference for changing.
    If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Reply With QuoteRegister To Reply
  5. 06-02-2016, 04:23 AM #5
    Dlinen
    Dlinen is offline
    Registered User
    Join Date
    05-27-2016
    Location
    Zurich
    MS-Off Ver
    Office 2016
    Posts
    61

    Re: Formula that takes data from changing worksheets

    Thanks guys, the indirect formula worked.

    Is there any reason why you don't suggest the first strategy? It would remove one manual step and automate the data pull for me.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Dragging formula down a column that takes the data from a row
    By flapface in forum Excel General
    Replies: 5
    Last Post: 10-09-2014, 07:24 AM
  2. It takes 1-2 minutes to respond when changing a value of cell
    By jayce_sos in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-19-2014, 03:00 AM
  3. Formula that takes a column(s) of data and inserts it into table
    By 1454 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-22-2013, 09:46 AM
  4. Debugging this error in this macro that takes worksheets and places them into new workbook
    By ibabs in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 02-17-2013, 06:47 PM
  5. [SOLVED] Formula that takes takes out a specific word/number from text string
    By ncalvelo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-29-2012, 10:21 AM
  6. Updatable Range, or formula to look up changing worksheets?
    By oo0tommyk0oo in forum Excel General
    Replies: 0
    Last Post: 02-18-2011, 06:24 PM
  7. Create a chart that takes a 'snapshot' of changing data
    By TS057 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-20-2010, 10:02 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1