#1
|
|||
|
|||
Automatically populate cell with data from column in sequential order
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. |
#2
|
|||
|
|||
Quote:
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? Quote:
|
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
Thank you! That was super helpful
|
#6
|
||||
|
||||
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: Code:
=IFERROR(OFFSET(INDIRECT(MID(FORMULATEXT($E$2),2,99)),SUM($E$1:E$1),0,F$1),"") 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: Code:
=OFFSET(INDIRECT($E$2),SUM($E$1:E$1),0,F$1) |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Drop Down List to Automatically Populate Data in a Table | src144 | Word VBA | 8 | 12-03-2020 04:15 PM |
How to auto-populate data based on cell value | ag2686 | Excel | 2 | 06-13-2018 09:19 PM |
Automatically Organize data in a sheet differently (in order to create a dynamically updated graph) | carlos_cs | Excel | 3 | 05-04-2016 08:44 AM |
How to populate cells in Sheet2 with Data Source query using cell data from Sheet1 | bobznkazoo | Excel | 2 | 03-27-2014 11:14 AM |
Populate Data to another cell | Mrkieth | Excel | 2 | 04-29-2013 07:30 AM |