+ Reply to Thread
Results 1 to 2 of 2

Copy and Pasting formula without change SOME cell references, while allowing others change

  1. #1
    Registered User
    Join Date
    06-11-2019
    Location
    California
    MS-Off Ver
    365
    Posts
    3

    Copy and Pasting formula without change SOME cell references, while allowing others change

    So I'm trying to make life easy with a formula I've been working on.

    My formula is =SUMIFS(H2:H27282,G2:G27282,"1",F2:F27282, "*"&OFFSET(L2,0,-1)&"*")

    Essentially in the H Column I have the Minutes of a task which I'm trying to total up. in the G Column I have the "Task Codes" 1,2,3,4,5,6,7,8, which all represent a specific task like walking, running, jumping jacks, ect. Then in the F column I have names of the person doing those tasks.The point of this excel document is to track the number of minutes these people are doing various tasks over the course of a year. As you may expect, there will be many entires under the same people and the same task codes. By the end of the year, I'll have thousands of entry logs to report on. It's for this reason I created the K column to list the names of every member (Only once) included on this list. The K Column is my reference list and I use the L Column with the OFFSET function to directly report the total minutes that person in Column K is doing the identified "Task Code". In this formula I showed, the Code I'm reporting on is "1".

    THE DILEMMA: To report the total minutes in column L. I don't want to have to go into the formula each time and change the OFFSET reference to the cell bellow it. If I have 300 people participating in this tracking report, It's going to be an hour of chaning L2 to L3, and over and over and over again until I reach L300.

    WHAT I'M WONDERING: I'm curious if I can use the Copy drag function to my advantage or do something similar. When I copy and drag, all the cell references +1.
    So in effect, if in row 2 the correct formula is =SUMIFS(H2:H27282,G2:G27282,"1",F2:F27282, "*"&OFFSET(L2,0,-1)&"*") then when I copy drag down I get:

    =SUMIFS(H3:H27283,G3:G27283,"1",F3:F27283, "*"&OFFSET(L3,0,-1)&"*")

    So I like the L reference moves to 3. However I want the H, G, and F reference to stay 2.

    THE QUESTION: Is there a way to copy and paste this formula into all the rows of L, while maintaining the references for H,G, and F, but allowing L to continualy increase per cell it's draged into?
    Last edited by ireview; 06-13-2019 at 06:52 PM.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Copy and Pasting formula without change SOME cell references, while allowing others ch

    Yes.

    Use absolute referencing with "$".

    Like this

    =SUMIFS($H$3:$H$27283,$G$3:$G$27283,"1",$F$3:$F$27283, "*"&OFFSET(L3,0,-1)&"*")

    You can read up on Absolute references here.

    This is fundamental to Excel and will save you hours of work.
    Dave

+ 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: 09-07-2018, 10:07 AM
  2. How can I change multiple cells at once to change where a formula references?
    By spenser1235 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-13-2018, 10:07 PM
  3. [SOLVED] using a macro to copy a worksheet but change formulas cell references
    By Jamidd1 in forum Excel Programming / VBA / Macros
    Replies: 28
    Last Post: 02-17-2016, 02:31 PM
  4. How to lock a cell only not allowing user change format?
    By ohlalayeah in forum Excel General
    Replies: 5
    Last Post: 07-26-2012, 09:24 AM
  5. Replies: 3
    Last Post: 12-22-2010, 01:52 PM
  6. A way to get the cell references in a formula change
    By Michael6 in forum Excel General
    Replies: 9
    Last Post: 09-09-2009, 05:45 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