+ Reply to Thread
Results 1 to 15 of 15

Basic Offset Formula Help!

  1. #1
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Basic Offset Formula Help!

    Hi,

    Im trying to figure this "Offset" in my problem out.

    At least I think I should use offset

    I know its a simple fix, but Im running out of time to get it solved.

    I think I can post my problem without an example attachment, I hope its not to confusing the way I wrote it out below.

    Thank You for help in advance,

    Problem I have is as follows:

    I have "Sheet 1" in excel that has a list of numbers that could be any number "In my Columns"

    Example: Column E, Rows 6,7,8,9,10

    -141, -132, -137, -142, -131

    Ok, In my "Sheet 2" the above numbers are in column C, Rows 6,10,14,18,22

    You will notice in "Sheet 2" the Rows of the numbers from "Sheet 1" do not go in rows like "Sheet 1" (6,7,8,9,10)

    they will go up every "4 Rows" (6,10,14,18,22)

    Also there is numbers in the other 3 rows that are skipped on "Sheet 2" (These numbers are not a problem, except for possibly not showing blank cells in formula"

    All I'm doing is transferring numbers from "Sheet 1" to "Sheet 2"

    I use the Basic formula for 1st number as =Sheet1!E6 in "Sheet 2" to get number from "Sheet 1" which is -141

    But when I copy and paste on "Sheet 2" the 2nd number it will skip the -132, -137, -142 and will paste the -131 which is the "4th number down on Sheet 1"

    I understand why it does this, but I don't know how to fix so it will show the 2nd number -132, then 4 more rows down show the 3rd number -137 and so on.

    Thank You for Help!
    Attached Files Attached Files
    Last edited by Brian.Aerojet; 03-26-2017 at 01:15 AM.

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Basic Offset Formula Help!

    Hi, welcome to the forum

    I tried to follow along, but got lost

    Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Re: Basic Offset Formula Help!

    I uploaded an example, Most of the numbers are different from what I had in original post.

    Also, the numbers in yellow have nothing to do with my expected outcome, everything in red is what i have a problem with

    Thanks

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Basic Offset Formula Help!

    Sorry for the delay, I was AFK

    Im not sure what you will have in between those valuesm but try this...
    =IF(MOD(COUNT($O$5:O5),4)>0,"",INDEX(Sheet1!E:E,6+INT((ROWS($1:1)-1)/4)))

    If you want to reference something else in the "gaps", replace the "" with what you need

  5. #5
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Re: Basic Offset Formula Help!

    Ford,
    Thanks for help,
    I do have a question, where does the ($O$5:O5) come from?
    I put formula in my excel sheet and changed the sheet 1 in your formula to my sheets name which is Digit Total, but it didn't work like it did in the sample attachment I sent you, it work perfect in the sample

    Thanks

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Basic Offset Formula Help!

    Quote Originally Posted by Brian.Aerojet View Post
    Ford,
    Thanks for help,
    I do have a question, where does the ($O$5:O5) come from?
    I put formula in my excel sheet and changed the sheet 1 in your formula to my sheets name which is Digit Total, but it didn't work like it did in the sample attachment I sent you, it work perfect in the sample

    Thanks
    oops apologies, that was a range I was using to test with It needs to be the range where that formula goes - although, if you have stuff in between, that may not work.

    *from the Sort Circuit movie - malfunction, need input*

  7. #7
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Re: Basic Offset Formula Help!

    You're correct, I do have stuff in-between, Im getting errors.

    What do I need to do?

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Basic Offset Formula Help!

    Again, sorry for the delay

    Can you upload a small WB showing (realistically) what would be in all of those rows?
    Or, tell me - Im specifically looking for something to "hook onto" that will let me know which rows to (or not to) apply the formula to

  9. #9
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Re: Basic Offset Formula Help!

    I will upload a copy later this evening,
    I wish I had time to upload now, unfortunately I don't.

    I might be able to get it to you sooner if I can find a little time today
    Again, thanks for your help

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Basic Offset Formula Help!

    You can use this formula in cell C6 of Sheet2 in the file you posted in Post #1:

    =INDEX(Sheet1!$E:$E,INT((ROWS($1:1)-1)/4)+6)

    then copy it into C10, C14, C18 etc.

    Hope this helps.

    Pete

  11. #11
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Re: Basic Offset Formula Help!

    I'm getting REF when I put that in as my formula Pete

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Basic Offset Formula Help!

    Well, here's your file with the formula in. Check out the formulae in C6, C10, C14 etc.

    Hope this helps.

    Pete
    Attached Files Attached Files

  13. #13
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Re: Basic Offset Formula Help!

    Thank You Ford and Pete for helping me with my formula.

    I finally got it to work, the formula worked with the file I uploaded, but it didn't work with my original file.
    My original wasn't named Sheet 1, it was actually named Digit Total.
    For some reason, soon as I changed that sheet name, it worked perfect. I didn't have to change name of sheet 2.
    If you get a chance, do you care to let me know why this may have happened?

  14. #14
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Re: Basic Offset Formula Help!

    Also, I wanted to ask if I could add all these numbers together, but in a different cell right beside the -141,-132,etc. that will also move the same as the other formula did (4 Rows down each time)

    Example would be from the excel file I downloaded before (Sheet2), but look to the right of the -141,-132,-137,-142,etc. you will see the -9367,-9476,-9436,-9530.

    These numbers come from the column F in sheet 1. Only difference is I add these numbers to the other numbers. Note: the numbers in the file I uploaded are just random numbers, not actually the numbers Im using.

    I hope Im not confusing you, but I bet I am.

    Another example would on sheet 2, take the C22 # (-131) and add the C22 # (-9530) together, this would give you -9661 would go in D22 Cell


    I didn't know if I needed to show the question from before as solved and then post this question.

    Let me know if I do for future reference

    Thanks,
    Brian

  15. #15
    Forum Contributor Brian.Aerojet's Avatar
    Join Date
    03-25-2017
    Location
    TN-USA
    MS-Off Ver
    2016 365
    Posts
    173

    Re: Basic Offset Formula Help!

    I figured out the formula I need from the post I made last. Here is what I come with, and it actually worked, lol

    =SUM(Sheet5!$F$6:INDEX(Sheet5!$F:$F,207+INT((ROWS($209:214)-1)/4+6)))

    I will show as Thread Solved

    Thanks,
    Brian

+ 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. Using a cell containing a VLookup Formula in a Basic Multiplication Formula
    By slampert in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-11-2015, 09:20 PM
  2. I need help with a basic IF formula
    By bgpzone in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2015, 06:51 AM
  3. [SOLVED] Basic 'If' Formula
    By Jason Kaid in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-24-2014, 01:47 PM
  4. Replies: 1
    Last Post: 03-04-2012, 12:03 AM
  5. Reformatting - insert formula with offset, then offset to next cell
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-06-2011, 05:41 AM
  6. Excel 2007 : Basic Formula help
    By Bcf0514 in forum Excel General
    Replies: 1
    Last Post: 07-09-2008, 08:35 PM
  7. [SOLVED] How to apply OFFSET as the range in a basic 'Copy' process...
    By cdavidson in forum Excel General
    Replies: 4
    Last Post: 11-08-2005, 04:50 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