+ Reply to Thread
Results 1 to 10 of 10

index formula needed--how to get a formula to skip columns

  1. 10-01-2012, 02:44 PM #1
    rsmidtisu
    rsmidtisu is offline
    Registered User
    Join Date
    10-01-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    index formula needed--how to get a formula to skip columns

    I am trying to drag/copy my formula but need it to skip 3 columns from the previous worksheet across.

    Example:
    Column B= =(Daily!B3-Daily!C3)
    Column C= =(Daily!E3-Daily!F3)
    Column D= =(Daily!H3-Daily!I3)

    How can I get this so I can drag over multiple columns while having it continue to skip 3 columns at a time?

    Thanks!
    Last edited by rsmidtisu; 10-02-2012 at 11:33 AM.
    Reply With QuoteRegister To Reply
  2. 10-01-2012, 03:15 PM #2
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: how to get a formula to skip columns

    =index(daily!$b:$z,row(a3),column(a1)*3-2)-index(daily!$b:$z,row(a3),column(a1)*3-1)
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  3. 10-01-2012, 04:09 PM #3
    rsmidtisu
    rsmidtisu is offline
    Registered User
    Join Date
    10-01-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: how to get a formula to skip columns

    That worked for the first cell but when I drag over to copy it gives me the same value over and over. I have attached an example of what i need. So in this example when I copy the formula into cell F3 on the "total" sheet I need it to be =Daily!N3-Daily!O3.

    example.xlsx
    Reply With QuoteRegister To Reply
  4. 10-01-2012, 04:30 PM #4
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: how to get a formula to skip columns

    Martin's formula is correct. (I just checked it on your sheet). If it's not working for you, then maybe you have calculation set to manual (hit F9 to check) or you've typoed.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  5. 10-01-2012, 04:34 PM #5
    rsmidtisu
    rsmidtisu is offline
    Registered User
    Join Date
    10-01-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: how to get a formula to skip columns

    Using it with hitting F9 worked!! Thanks so much. How can I get this to work without having to hit F9 everytime?

    Thanks!
    Reply With QuoteRegister To Reply
  6. 10-01-2012, 04:36 PM #6
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: how to get a formula to skip columns

    File>Options>Formulas> Workbook Calculations> Automatic
    Find it?
    Reply With QuoteRegister To Reply
  7. 10-02-2012, 09:24 AM #7
    rsmidtisu
    rsmidtisu is offline
    Registered User
    Join Date
    10-01-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: how to get a formula to skip columns

    Yes! thank you!
    Reply With QuoteRegister To Reply
  8. 10-02-2012, 10:08 AM #8
    rsmidtisu
    rsmidtisu is offline
    Registered User
    Join Date
    10-01-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: how to get a formula to skip columns

    Can I add on to this question? I need 2 more index formulas created and can't seem to get them myself

    The first index formula on this example is completed but I need one for the "IF THEN" formula and the percentage formulas on the Total sheet. They also need to skip 3 columns at a time like the previous one did.

    In the IF formula in cell F20 needs to be =IF(Daily!P3>0,"NO",IF(Daily!P3=0,"YES"))

    The % formula in cell F35 needs to be =1-(Daily!P3/Daily!N3)

    I have attached the example as well.

    Thanks!!

    example.xlsx
    Reply With QuoteRegister To Reply
  9. 10-02-2012, 11:08 AM #9
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: index formula needed--how to get a formula to skip columns

    In B20

    =IF(INDEX(Daily!$B:$Z,ROW(A3),COLUMN(A1)*3)>0,"NO",IF(INDEX(Daily!$B:$Z,ROW(A3),COLUMN(A1)*3)=0,"YES"))

    In B35

    =1-(INDEX(Daily!$B:$Z,ROW(A3),COLUMN(A1)*3)/INDEX(Daily!$B:$Z,ROW(A3),COLUMN(A1)*3-2))
    Reply With QuoteRegister To Reply
  10. 10-02-2012, 11:22 AM #10
    rsmidtisu
    rsmidtisu is offline
    Registered User
    Join Date
    10-01-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: index formula needed--how to get a formula to skip columns

    Thank you so much! you are the best!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1