+ Reply to Thread
Results 1 to 3 of 3

Help with stepping across columns

  1. #1
    Registered User
    Join Date
    03-15-2012
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    2

    Help with stepping across columns

    Hi,

    I'd be very grateful for any assistance with a, for me, odd problem that I'm blocking on.

    Explanation:
    I have a sheet, let's call it 'data', which has values in, lets say A10 through to Z10. These values are just simple decimals, nothing complicated, just typed in values.

    I have another sheet, let's call it 'summary' which needs to show values selected from the data sheet but with intervals or 'steps' of five apart. For example:
    Cell A10 in the summary sheet is a very simple reference to A10 in the 'data' sheet. So I use { ='data'!$A$10 } and that brings the value from A10 in the data sheet into A10 on the 'summary' sheet.
    So far so easy.

    Now, in cell B10 of the 'summary' sheet I need to show the value which is in F10 of the 'data' sheet. In cell C10 of the 'summary' sheet I need to show the value in cell K10 of the 'data' sheet.

    I need to show values with an 'interval' or 'step' of five columns apart.....

    I have been using OFFSET like this in B10: { =OFFSET('data'!$A$10,0,5) } And that works.. just a dead simple offset of 5 columns from the A10 cell in the data sheet.

    My problem is that, it does not scale easily (or does it?). For each cell I drag that formula into I need to manually edit the cell reference and or the offset column value.

    I was hoping that there was a way to say in cell B10 of the summary sheet "Look at the cell referenced in the formula in cell A10 (Which is cell A10 in the data sheet) add five columns to it and return that value.

    Then, if I dragged that formula across into C10 it would be interpreted as "Look at the cell referenced in cell B10 (which would be cell F10 in the data sheet, because I need to jump forward five columns to get the right value). Then in cell C10 of the summary sheet it would look at cell B10, work out which cell is referenced in the formula (F10), then step forward to cell K10 and so on.

    Essentially is there any way to look at the cell being referenced in another formula, add five columns on to it and return that value?

    Sorry if I have mangled the explanation. Would be very grateful for any assistance! - thanks!
    Stuart
    Last edited by kryten68@googlemail.; 12-11-2019 at 06:40 PM.

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,251

    Re: Help with stepping across columns

    Hi,

    You could use the column number to generate the offset, eg, in A10 of the sheet 'summary'

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


    dragged across should give you what you need.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    10,981

    Re: Help with stepping across columns

    Or perhaps read the yellow banner...
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

+ 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. Code works fine when stepping through code but Excel freezes if I run it without stepping
    By OldManExcellor in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-19-2016, 10:39 AM
  2. Replies: 11
    Last Post: 07-07-2012, 09:14 AM
  3. stepping thru XL files
    By sydmil in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-19-2009, 05:53 PM
  4. stepping chart
    By Petje in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 07-07-2006, 05:22 PM
  5. [SOLVED] Stepping Through vs. Play
    By CWillis in forum Excel General
    Replies: 1
    Last Post: 06-15-2006, 12:40 PM
  6. [SOLVED] Stepping through Code
    By ben in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-08-2006, 05:50 PM
  7. Stepping Problem,
    By ben in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-08-2006, 12:25 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