+ Reply to Thread
Results 1 to 7 of 7

Offset not working

  1. #1
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    108

    Offset not working

    I am trying to use Offset and it doesn't seem to be displaying what I want.

    Cell F1
    Please Login or Register  to view this content.
    I have tried changing $ to 5,6. I tried changing the B9 to A9 and I even tried swapping B9 with F5.

    This offset code worked on a different sheet quite fine so I think I must have not done something or did something.
    Attached Files Attached Files

  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: Offset not working

    Try changing your formula to
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    or try this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by FlameRetired; 01-04-2022 at 08:11 PM.
    Dave

  3. #3
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    108

    Re: Offset not working

    Thanks

    But why does the formula need to be different when it worked elsewhere. I've uploaded a new file . on Sheet 2 Cell H1 it works.

    I am so confused
    Attached Files Attached Files

  4. #4
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    China Shanghai
    MS-Off Ver
    365 V2402 and WPS V2022
    Posts
    3,327

    Re: Offset not working

    ans post#1
    Cell F1 formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    ans post#3
    Cell H1 formula
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    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: Offset not working

    The formula for determining the offset row is determined by =COUNTIF(B20:B79,"<="&$C$1) ie the range is set 1 row lower than the actual range of =COUNTIF(B19:B79,"<="&$C$1) Since the range in the second example is already offset it works. (offsetting errors)

    Your range in the first example is =COUNTIF(B9:B79,"<="&$C$3). In order for that to work as before you would need to offset that range =COUNTIF(B10:B79,"<="&$C$3). This is not an approach I would advise.

    Try playing with it a bit. The term OFFSET literally means OFFset from the origin (ie the first argument) ... ie an offset of 1 is the row below the row of origin.

  6. #6
    Forum Contributor
    Join Date
    12-30-2021
    Location
    Mount Gambier, Australia
    MS-Off Ver
    19
    Posts
    108

    Re: Offset not working

    Thanks Dave.

    That makes a lot of sense. I only come across OFFSET a few days ago.

  7. #7
    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: Offset not working

    Ah OK. It took me a while to catch on to it the first few times as well.

    You are welcome. Glad to help. Thank you for the feedback, added rep and marking your thread Solved.

+ 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] Offset formula not working together with if
    By SstixX in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-17-2020, 12:18 PM
  2. offset not working
    By Learning ExL in forum Excel - New Users/Basics
    Replies: 22
    Last Post: 04-19-2015, 05:54 PM
  3. [SOLVED] Can anyone tell me why this isn't working? .offset
    By Taemex in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-12-2014, 07:11 PM
  4. Offset not working
    By sput_sput in forum Excel General
    Replies: 12
    Last Post: 08-25-2010, 02:51 PM
  5. [SOLVED] offset not working
    By BorisS in forum Excel General
    Replies: 2
    Last Post: 11-30-2005, 04:20 AM
  6. Offset now working?
    By sleah in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-08-2005, 01:57 AM
  7. Offset Not Working
    By sleah in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-07-2005, 05:56 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