+ Reply to Thread
Results 1 to 3 of 3

Update Cell Range when Copy/Pasting Formula

  1. #1
    Registered User
    Join Date
    07-02-2020
    Location
    Boulder, CO
    MS-Off Ver
    2016
    Posts
    2

    Update Cell Range when Copy/Pasting Formula

    Hello,

    I have a template with formulas that I frequently copy into new sheets, but I don't know how to write the formulas so that they update to reflect a new range once copied.

    Example of template formula:
    =SUMIFS(L4:L6,A4:A6,"*cost*",Q4:Q6,"main")

    Example of desired update once copied:
    =SUMIFS(L2:L627,A2:A627,"*cost*",Q2:Q627,"main")


    The columns don't ever change, but the total row count does (row 1 is always headers). Is there a way to write the formulas so that pasting the template formula will update the range from 4:6 to 2:the last row with data? Some sort of CellUp function perhaps?

  2. #2
    Forum Contributor
    Join Date
    04-24-2020
    Location
    Woodbridge, VA
    MS-Off Ver
    2016
    Posts
    116

    Re: Update Cell Range when Copy/Pasting Formula

    Can you just leave the cell number out?

    =SUMIFS(L:L,A:A,"*cost*",Q:Q,"main")

  3. #3
    Registered User
    Join Date
    07-02-2020
    Location
    Boulder, CO
    MS-Off Ver
    2016
    Posts
    2

    Re: Update Cell Range when Copy/Pasting Formula

    Thank you, mikeava, that worked - I did have to move around some of the totals to prevent circular references from being created in the cells with the formulas, but that part of my spreadsheet is flexible. Thanks, I hadn't even thought of trying that trick!

+ 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. [SOLVED] VBA to copy a formula from a cell range and paste it to a cell range based on date
    By dedark05 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-19-2019, 04:41 AM
  2. Copy and pasting a range using autofilter and input box
    By sx200n in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-14-2015, 08:42 AM
  3. Copy and pasting a dynamic range from one sheet to another
    By drpurple in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-05-2014, 09:44 AM
  4. Replies: 2
    Last Post: 03-25-2011, 06:37 AM
  5. Replies: 2
    Last Post: 08-15-2008, 09:34 AM
  6. Replies: 1
    Last Post: 05-17-2008, 09:30 AM
  7. Chart in Cell & Copy/Pasting Charts w/auto range change
    By longfisher in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 04-01-2008, 05:41 AM

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