+ Reply to Thread
Results 1 to 17 of 17

"Letter" won't change in my formula when I copy it into other cells

  1. #1
    Registered User
    Join Date
    09-10-2022
    Location
    Stuttgart
    MS-Off Ver
    Microsoft Excel 2021
    Posts
    7

    "Letter" won't change in my formula when I copy it into other cells

    Hey everybody,

    I have a problem that seems pretty simple but I can't figure out what the issue is.

    I have two sheets in my workbook. In one I write all the formulas and the other one is where all the data is located (I called it "Dataset" in this example).

    I am trying to calculate the maximum of a given Column from the sheet "Dataset" using this formula "=MAX(OFFSET('Dataset'!A3;0;1;45300;1))".


    Let's say I write this formula in cell A1 of my sheet with all the formulas. When I drag down the formula I now want the formula to chance "A3" to "B3" so I can calculate the maximum of all the numbers in Column B of my "Dataset" sheet.


    However I can't figure out how to write the formula so that "A3" becomes "B3" when dragging down the formula.



    Any help would be greatly appreciated






    Edit: I added a small dataset to better show you what I mean.

    What I would like to do is use a formula like the one I wrote down in the example attachment but when I drag the formula down I want the column reference to change from A to B meaning that I now want to calculate the MAX of all the values in Column B of "Dataset".
    Attached Files Attached Files
    Last edited by Destimmy; 09-10-2022 at 03:32 AM. Reason: Added small dataset

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: "Letter" won't change in my formula when I copy it into other cells

    Do you mean:

    =MAX(OFFSET('Dataset'!A3;0;1;45300;1))

    If so:

    =MAX(OFFSET(INDEX(Dataset!$A$3:$F$3;ROWS(A$1:A1));0;1;45300;1))

    in other sheet, A1, copied down. Adjust F3, as needed.

    However, I suspect you really mean:

    =MAX(OFFSET(INDEX(Dataset!$A$3:$F$3;ROWS(A$1:A1));0;0;45300;1))
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: "Letter" won't change in my formula when I copy it into other cells

    If you drag your formula to the right I expect A3 changed in B3.


    If not:

    HOW TO ATTACH YOUR SAMPLE WORKBOOK:

    Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Registered User
    Join Date
    09-10-2022
    Location
    Stuttgart
    MS-Off Ver
    Microsoft Excel 2021
    Posts
    7

    Re: "Letter" won't change in my formula when I copy it into other cells

    Thank you for your reply Glenn

    Your suggestion didn't work unfortunately. I added a small example dataset to the thread maybe this will make it a little bit clearer.

    I'd really appreciate it if you gave it another look. I don't think that the fix is very difficult and maybe my excel is just bugging out... idk

  5. #5
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: "Letter" won't change in my formula when I copy it into other cells

    I think the formula works as expected, see the green cells.

    I checked the data using a pivot table (see the green cells).


    See the attached file.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: "Letter" won't change in my formula when I copy it into other cells

    What is yyour expected answer (in THIS sheet) in all 5 columns.

    IMHO you are overcomplicating this.

    =MAX(INDEX(Dataset!$A$1:$E$98,,ROWS(Tabelle1!A$2:A2)))

    Why are you starting from a3, not A1? If you REALLY want to start from row 3, change to:

    =MAX(INDEX(Dataset!$A$3:$E$98,,ROWS(Tabelle1!A$2:A2)))

    We need more information on the overall aim. If you need 45,000 rows, then change to:

    =MAX(INDEX(Dataset!$A$1:$E$45000,,ROWS(Tabelle1!A$2:A2)))
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    09-10-2022
    Location
    Stuttgart
    MS-Off Ver
    Microsoft Excel 2021
    Posts
    7

    Re: "Letter" won't change in my formula when I copy it into other cells

    THANK YOU!!! I'm so stupid I didn't realize I had to drag the formula horizontally in order to change the column instead of the row....

    Is there any way that I can change the column in the formula instead of the row when I drag the formula vertically?


    Again thank you so much for your efforts!

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: "Letter" won't change in my formula when I copy it into other cells

    Your current formula calculates MAX of column B

    Use

    =MAX(OFFSET(Dataset!$A$3,0,ROWS($1:1)-1,45300,1))

    Drag DOWN
    Last edited by JohnTopley; 09-10-2022 at 03:55 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: "Letter" won't change in my formula when I copy it into other cells

    You said:

    Is there any way that I can change the column in the formula instead of the row when I drag the formula vertically?


    I don't understand. Show me in a sheet.

  10. #10
    Registered User
    Join Date
    09-10-2022
    Location
    Stuttgart
    MS-Off Ver
    Microsoft Excel 2021
    Posts
    7

    Re: "Letter" won't change in my formula when I copy it into other cells

    Sorry I think you're right and I made things too complicated.

    All I really wanted to know is how I need to change my formula so that the column name changes instead of the row name when I drag the formula down vertically. By now I realized that everything works fine if I drag the formula horizontally so I guess the last question is if there is any way I can change up my formula so that the column name changes instead of the row name when I drag it down vertically because that is how I set everything up


    Again, sorry for making this a lot more difficult than it had to be!

  11. #11
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: "Letter" won't change in my formula when I copy it into other cells

    @Destimmy

    If you are responding out of sequence, it is usually enough just to mention the helper's user name (e.g @Oeldere).

    In this case that could be handy, since you got replies of 3 forummembers.

  12. #12
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: "Letter" won't change in my formula when I copy it into other cells

    See attached
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    09-10-2022
    Location
    Stuttgart
    MS-Off Ver
    Microsoft Excel 2021
    Posts
    7

    Re: "Letter" won't change in my formula when I copy it into other cells

    Quote Originally Posted by JohnTopley View Post
    Your current formula calculates MAX of column B

    Use

    =MAX(OFFSET(Dataset!$A$3,0,ROWS($1:1)-1,45300,1))

    Drag DOWN
    Ok JohnTopley thank you for your response! It works great! Could you explain to me what exactly "ROWS($1:1)-1" does and why everything is working now?

    I literally just typed it in but don't really understand why it works


    Thanks again!

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: "Letter" won't change in my formula when I copy it into other cells

    SHOW me in a sheet.

  15. #15
    Registered User
    Join Date
    09-10-2022
    Location
    Stuttgart
    MS-Off Ver
    Microsoft Excel 2021
    Posts
    7

    Re: "Letter" won't change in my formula when I copy it into other cells

    Quote Originally Posted by Glenn Kennedy View Post
    SHOW me in a sheet.

    What do you want me to show you in a sheet I don't understand where the missunderstanding is. There has been a great solution to my problem maybe that clears things up I don't know how to explain it better

  16. #16
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,003

    Re: "Letter" won't change in my formula when I copy it into other cells

    The OFFSET parameter "Cols" determines the column to the left or right of your "Reference": in this case, it is A3.

    The ROWS($1:1) acts as a counter so ROWS($1:1)=1, ROWS($1:2)=2 etc. so as you drag the formula down you get values of 1,2,3 ....

    We subtract 1 because we want the first column referenced to be column A which is an offset (to the RIGHT) of 0 from A(3); on the second row "Cols" (in OFFSET) is now 2 -1 =1 which is Column B.

    and so on

    Hope this helps.

    Use "Formulas" .. "Evaluate Formula" to see how this works.

  17. #17
    Registered User
    Join Date
    09-10-2022
    Location
    Stuttgart
    MS-Off Ver
    Microsoft Excel 2021
    Posts
    7

    Re: "Letter" won't change in my formula when I copy it into other cells

    Quote Originally Posted by JohnTopley View Post
    The OFFSET parameter "Cols" determines the column to the left or right of your "Reference": in this case, it is A3.

    The ROWS($1:1) acts as a counter so ROWS($1:1)=1, ROWS($1:2)=2 etc. so as you drag the formula down you get values of 1,2,3 ....

    We subtract 1 because we want the first column referenced to be column A which is an offset (to the RIGHT) of 0 from A(3); on the second row "Cols" (in OFFSET) is now 2 -1 =1 which is Column B.

    and so on

    Hope this helps.

    Use "Formulas" .. "Evaluate Formula" to see how this works.

    Yeah ok that makes sense. Man I can't tell you how much you helped me here I was stuck on this for hours and getting hopeless :D

    Cheers, thanks again!

+ 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: 9
    Last Post: 01-06-2022, 10:08 PM
  2. Auto Change Date format from "." to "/" when copy data from SAP T-code - VF05
    By abhinavbinkar in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-29-2019, 05:20 AM
  3. [SOLVED] Copy cells from range "C3" to "F3" with loop and blank field
    By masterm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-16-2015, 01:03 PM
  4. Replies: 3
    Last Post: 01-12-2015, 02:11 PM
  5. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  6. Change "Parent" cell based on results of multiple "Children" cells
    By ccowman in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 12-14-2012, 02:33 AM
  7. Changing first letter to caps, letters after "-" and "/", and rest to small letter.
    By rhexis07 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-17-2008, 08:03 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