+ Reply to Thread
Results 1 to 5 of 5

Not sure how to describe: Reference cell in other worksheet for sum_range

  1. #1
    Registered User
    Join Date
    07-09-2019
    Location
    SF, CA
    MS-Off Ver
    1906
    Posts
    5

    Not sure how to describe: Reference cell in other worksheet for sum_range

    I'm kind of an Excel newbie and am trying to figure something out with SUMIF. I googled around a bit and found out about INDIRECT although I dont think its exactly what I need.

    I have a SUMIF formula that is referencing a separate spreadsheet.

    The sum_range portion of it is '[Worksheetname.xlsx]Sheetname'$359:$359)

    What I am trying to do is change the $359:$359 to a value in the current spreadsheet. ie. I populate cell B61 with the number 475, so the formula could instead be: '[Worksheetname.xlsx]Sheetname'$B61:$B61) which would effectively yield '[Worksheetname.xlsx]Sheetname'$475:$475). I want it to reference a cell so I can drag the formula down and have it change to whatever numbers are in B62, B63, etc...

    Does this make sense and is it possible?

    Thanks

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Not sure how to describe: Reference cell in other worksheet for sum_range

    Hello and welcome to the forum.

    The big limitation of INDIRECT is that it will only work when the object sheet is open in memory.

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

  3. #3
    Registered User
    Join Date
    07-09-2019
    Location
    SF, CA
    MS-Off Ver
    1906
    Posts
    5

    Re: Not sure how to describe: Reference cell in other worksheet for sum_range

    Hi Richard thank you for the response

    Here is a very dumbed down version on what I am trying to do

    I am trying to be able to select cells B3:D3 in Book3 and be able to drag it down, so that B4:D4 in Book3 would sum from sum_range B8:D8 in Book2
    Attached Files Attached Files

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    5,950

    Re: Not sure how to describe: Reference cell in other worksheet for sum_range

    Try this in B3 of Book3:

    =SUMPRODUCT(('[Book2.xlsx]Sheet1'!$A$3:$A$8=$A3)*('[Book2.xlsx]Sheet1'!$B$2:$D$2=B$2)*('[Book2.xlsx]Sheet1'!$B$3:$D$8))

    or

    =INDEX('[Book2.xlsx]Sheet1'!$B$3:$D$8,MATCH($A3,'[Book2.xlsx]Sheet1'!$A$3:$A$8,0),MATCH(B$2,'[Book2.xlsx]Sheet1'!$B$2:$D$2,0))

    Drag the formula to the right and down.

  5. #5
    Registered User
    Join Date
    07-09-2019
    Location
    SF, CA
    MS-Off Ver
    1906
    Posts
    5

    Re: Not sure how to describe: Reference cell in other worksheet for sum_range

    Hi falcondude thanks for your response

    When I paste that in it gives me the error, "not trying to type a formula? when there is an = excel thinks its a function. put a ' to get around this". I tried moving the apostrophes around but can't figure it out.

    The reason I am using SUMIF is because in the full workbook, there are breaks in the columns between years. ie. B2 is Jan 18, C2 is Feb 18, D2 is Mar 18, but then G2 is Apr 18. My initial inclination is to think that dragging a SUMPRODUCT formula would cause issues with that formatting.

    EDIT: Didn't see the second part of your response. I am trying the =INDEX now. so far it seems to be working. I will play with it a bit more. thank you

+ 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: 2
    Last Post: 12-29-2016, 11:53 AM
  2. Replies: 5
    Last Post: 05-06-2015, 12:05 AM
  3. Reference cell in worksheet to copy paste cell range from different worksheet
    By gbatz in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-16-2014, 04:33 PM
  4. Formula for a table reference....not even sure how to describe....
    By thb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-11-2013, 09:39 AM
  5. Can I reference a cell to call for a sum_range?
    By heyjude16 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-27-2012, 03:19 PM
  6. Display data reference from another worksheet based on cell reference
    By Drew123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-18-2011, 03:52 PM
  7. [SOLVED] Cross reference summation and offset <- sorry can't think of better way to describe!
    By TheWJB@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-28-2006, 04:10 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