+ Reply to Thread
Results 1 to 4 of 4

Click/drag a formula pulling from differ sheets w/ excel auto changing sheet names only

  1. #1
    Registered User
    Join Date
    06-20-2018
    Location
    Austin, TX
    MS-Off Ver
    Microsoft Office 2013
    Posts
    2

    Click/drag a formula pulling from differ sheets w/ excel auto changing sheet names only

    Hi!
    Here’s what I’m trying to do. I have a budget workbook. In it, I have several worksheets and need to pull data from them into a summary sheet I’ve named “Dashboard”, the 1st sheet. The exact information for the month in the same exact cell throughout all of the Monthly Expenditures worksheets.
    Worksheet Names
    Sheet1 = Dashboard
    Sheet2 = AUG EX
    Sheet3 = SEPT EX
    Sheet 4 = OCT EX

    And so on…
    So, I know the formula I need to pull data from one sheet to another. It is as follows:
    =SUM('AUG EX'!$D$20:$E$20)
    The problem is that I would like to be able to click and drag the formula down where excel automatically inserts the next worksheet name as I drag/copy to the next cell. I actually have to do this several times and don’t really want to have to go in and change the sheet name for each one manually. Can anyone help? Is it possible? Or maybe you have a different way to accomplish what I need?

    Month Total Monthly Expenditures
    August =SUM('AUG EX'!$D$20:$E$20)
    September =SUM('SEPT EX'!$D$20:$E$20)
    October =SUM('OCT EX'!$D$20:$E$20)
    November =SUM('NOV EX'!$D$20:$E$20)
    December =SUM('DEC EX'!$D$20:$E$20)

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Click/drag a formula pulling from differ sheets w/ excel auto changing sheet names onl

    Hi, welcome to the forum

    No, it is not possible to drag a formula down and have it change sheet names. BUT there is another way!!

    If you put each sheet's name in a cell, 1 below the other, and perhaps adjacent to where the formula is, you can use INDIRECT to grab the sheet name in the formula
    With the formula in B2 (for example) and the sheet name in A2
    =SUM(indirect("'"&A2&"'!$D$20:$E$20")

    Or if you just want to put the month name in A2...
    =SUM(indirect("'"&A2&" EX'!$D$20:$E$20)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-20-2018
    Location
    Austin, TX
    MS-Off Ver
    Microsoft Office 2013
    Posts
    2

    Re: Click/drag a formula pulling from differ sheets w/ excel auto changing sheet names onl

    Hi FDibbins,

    It worked! Thank you so much. I've never used the INDIRECT function before. Learned something new.
    Also, thank you for the welcome.

    Warm Regards,
    Cilla

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Click/drag a formula pulling from differ sheets w/ excel auto changing sheet names onl

    I am happy to 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. Formula to look at sheets with changing names
    By satkin2 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-18-2015, 08:55 AM
  2. Replies: 1
    Last Post: 09-21-2014, 10:23 AM
  3. Populate Excel Userform with Sheet names with double click to open the sheet ??
    By Shacker in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 05-08-2013, 03:54 PM
  4. Auto Hide/Unhide Based on Results of IF Formula Pulling From Another Sheet
    By dolphinfan2k8 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-06-2012, 11:51 AM
  5. Replies: 2
    Last Post: 07-13-2012, 03:21 AM
  6. Replies: 1
    Last Post: 08-10-2011, 06:40 AM
  7. How do I click and drag a formula changing only 1 value?
    By scottghansen79 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 10-25-2010, 07:34 PM

Tags for this Thread

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