+ Reply to Thread
Results 1 to 6 of 6

Copying across Sumif Indirect formula with relative references?

  1. #1
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    450

    Copying across Sumif Indirect formula with relative references?

    I was wondering if there was a workaround for an issue i've been having.

    I have 2 tabs (lets call them X & Y) which i delete every month and replace with the copy/move tabs option via an import macro. The tab names stay the same. I've linked some information to tabs X & Y which get deleted and replaced causing #REF errors. Is there a way to prevent this without having to copy/paste the information into tabs X & Y?
    Last edited by Gti182; 02-18-2011 at 08:23 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: #REF error when deleting and replacing tabs

    You would need to use INDIRECT

    Please Login or Register  to view this content.
    when X is deleted the above would show #REF! but once replaced it would return to normal

    Note: INDIRECT is Volatile...see link in sig. for more info.

  3. #3
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    450

    Re: #REF error when deleting and replacing tabs

    thanks will look into it tomorrow and see how i get on.

  4. #4
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    450

    Re: #REF error when deleting and replacing tabs

    Ok managed to get the indirect formula to work but having trouble copying it across a row as the relative cells remain the same? In the formula below i need the D103:D125 to change to E103:D125 and F103:F125 etc if i copy to the right.

    =SUMIF(INDIRECT("Coal!$B$103:$B$125"), $B$203, INDIRECT("Coal!D103:D125"))

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Copying across Sumif Indirect formula with relative references?

    One of INDIRECT's nice features is that it supports R1C1 notation

    If we assume first SUMIF is in C203 (unknown) then:

    Please Login or Register  to view this content.
    This is much simpler than using hideous A1 style approaches to increment range references.

    I put together a basic overview of R1C1 notation a while ago here

    If the first formula column is not in Col C adjust the [1] accordingly
    to be the difference between the first column being referenced [D = 4] and the first formula column [C = 3] - hence 1 in the example

  6. #6
    Forum Contributor
    Join Date
    10-04-2010
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    450

    Re: Copying across Sumif Indirect formula with relative references?

    That my friend is awesome!! Always get excited when you post a reply because i know im going to learn something useful!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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