+ Reply to Thread
Results 1 to 6 of 6

How to change column letter in formula while dragging down?

  1. #1
    Forum Contributor
    Join Date
    11-25-2018
    Location
    Philippines
    MS-Off Ver
    Ms Office 365
    Posts
    217

    How to change column letter in formula while dragging down?

    As the title says, I wanted to changed the column letter while dragging down this formula:

    =IF($P$1=2019,SUMIFS(MOOE!$BM$4:$BM$1000,MOOE!$A$4:$A$1000,">12/31/2018",MOOE!$A$4:$A$1000,"<02/01/2019",MOOE!$I$4:$I$1000,"MOOE19"),IF($P$1=2020,SUMIFS(MOOE!$BM$4:$BM$1000,MOOE!$A$4:$A$1000,">12/31/2019",MOOE!$A$4:$A$1000,"<02/01/2020",MOOE!$I$4:$I$1000,"MOOE20")))

    to:

    =IF($P$1=2019,SUMIFS(MOOE!$BN$4:$BN$1000,MOOE!$A$4:$A$1000,">12/31/2018",MOOE!$A$4:$A$1000,"<02/01/2019",MOOE!$I$4:$I$1000,"MOOE19"),IF($P$1=2020,SUMIFS(MOOE!$BN$4:$BN$1000,MOOE!$A$4:$A$1000,">12/31/2019",MOOE!$A$4:$A$1000,"<02/01/2020",MOOE!$I$4:$I$1000,"MOOE20")))

    from BM to BN and so on.

    please techies.
    Last edited by archvanarl; 02-20-2019 at 04:01 AM.

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to change column letter in formula while dragging down?

    Try this formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    11-25-2018
    Location
    Philippines
    MS-Off Ver
    Ms Office 365
    Posts
    217

    Re: How to change column letter in formula while dragging down?

    It works but when it reach row 32, it will give an error #ref!.

    i have attached a sample sheet or reference.
    Attached Files Attached Files

  4. #4
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to change column letter in formula while dragging down?

    Try to change MOOE!$BM$4:$CM$1000 to MOOE!$BM$4:$ZM$1000 in both references.

  5. #5
    Forum Contributor
    Join Date
    11-25-2018
    Location
    Philippines
    MS-Off Ver
    Ms Office 365
    Posts
    217

    Re: How to change column letter in formula while dragging down?

    Thank you!!!

  6. #6
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: How to change column letter in formula while dragging down?

    You're welcome.

+ 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. Change the Column When Dragging
    By supdawg1985 in forum Excel General
    Replies: 17
    Last Post: 04-06-2014, 10:33 PM
  2. [SOLVED] Chaneg column letter when dragging down
    By johnmitch38 in forum Excel General
    Replies: 3
    Last Post: 10-28-2013, 09:32 AM
  3. Change column reference while dragging formula vertically
    By ekatecohn in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-18-2013, 07:23 PM
  4. When dragging a formula horizontally, how to change row instead of column?
    By Aderbalito in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-03-2013, 10:44 PM
  5. Getting the column letter to increase when dragging a =countif() function
    By Junior_Excel in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-11-2013, 04:54 PM
  6. [SOLVED] Formula to find a certain letter within a column and change value in another
    By MVS in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-02-2013, 08:12 PM
  7. Replies: 5
    Last Post: 08-21-2012, 03:36 PM

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