Hi - i would like to paste a heap of heart rate data in the 'B' column. Then in cell 'E13' choose a particular call to start at (i.e, =B19), and then the cell below that automatically is one below that that (i.e., B20) and so on down to the end of the green shade. The numbering would then continue in Column F, from F13, following the same pattern.I want it to automatically populate when I paste the heart rate data in column B, rather than having to drag the cells each time.



I hope I have explained this clearly. It seems like it would be an easy solution? Thanks for your help, it is appreciated.

Attached Images

Screen Shot 2020-12-08 at 2.16.37 PM.png (59.0 KB, 18 views)

What is this 'particular call'? I.e. how is decided, from which cell to start in every column from E13 and further?
a) you enter some value manually, and values are read starting from 1st cell in column A equal with entered value. What about cases when the value is not found - does this leave column empty, or will the formula e.g. look for 1st value in column A bigger than value in row 13?
b) You have somewhere stored row numbers for every column starting from column E. The formula will start at row 13 with reference to cell in column A, where row number equals with stored one.
c) some other?

I'd say it's contrary!

In cell E13, I want to select the number from column B where I am happy with the data....this will change depending on the data, but I think it's irrelevant to this discussion.



Basically, I want cell E14, to see that cell E13's text is =B19 (I have just selected this as an example, it could be B24 or anything in the B column), and so E14 knows that it needs to show the data that is listed one below it, in cell B20. Then cell E15 looks for the number in B21 and displays it, E16 looks for the number in B22 and so on and so on.


I want a forumla, so E14 and below (and the subsequent columns) automatically populates when i enter the reference cell in E13, rather than having to drag the formula each time.


hope this helps clarify?

To do this in way you described is possible only when you write your own UDF, like described here: vba - find cell reference of a cell from another cell in excel - Stack Overflow

To do this using built-in Excel formulas only, you have to enter row number(s) for start cell(s) somewhere, and use them to refer to wanted start cells. In added example is shown how to do this. Starting row numbers of worksheet are entered into Named Range nTimeRows.

This design isn't fully dynamic jet, as start row numbers are for worksheet rows. Whenever you add/remove rows above Tables tBPM and tDistr, you have to update row numbers in nTimeRows manually.

To make design fully dynamic, you have to store in nTimeRows row numbers of Table tBPM instead of worksheet row numbers. This of-course means, you have to change the formula in Table tDistr accordingly.

Attached Files

Example_Offset.xlsx (15.1 KB, 9 views)

Thank you! That was super helpful

In the attached (stolen from ArviLaanemets) there's a yellow cell E2 which currently has the formula pointing to cell B18. This is for your starting cell adjustment.
The numbers in the first row from columns F to J are the numbers of results you want in the columns below. For you to adjust.
The formulae which do the work are in cells F13 to J13. Depending on your version of Excel, you may have to array-enter these formulae (commit to the sheet using Ctrl+Shift+Enter rather than the more usual Enter), what's more you may have to select, for example, cells F13:F40 while array-entering that formula to cater for the greatest number of results you might want to see in that column; this might result in several #N/A errors in the cell which you could hide with:
The current versions of Excel don't need all that, just the entry of the formula into F13 alone.
Whichever way you have to do it, you can copy across.
I've used
MID(FORMULATEXT($E$2),2,99)
to get the address of a cell from the formula; you might find it easier to type in B18 into that cell without the preceding = sign and change the formula in F13 to a more straightforward:

Attached Files

msofficeforums46119Example_Offset.xlsx (12.0 KB, 13 views)