The ProblemLast weekend's email from Allen included a question from a reader about how to do a "reverse fill". He wanted to put in a formula, referring to the bottom cell in a vertical (column) range, then drag/auto-fill, as he went down he wanted the reference to move up.
NOTE: The following Answers all go back to the original question, regarding referencing from one column to another, using relative rows to transfer the information. The same principals apply to issues when transferring information from one set of columns to another set of columns or from a row to a column or from a column to a row.
- Copy the data from the source, in this case, from M150 and up
- At A1, PasteSpecial…Values if just the values are need, Paste Special...Link if the reference needs to be maintained.
- In B1 enter “=ROW()” the copy-down for all needed rows (or he can drag for the series)
- Copy B1:Bn and PasteSpecial…Values at B1 (skip if the ROW() function was not used in step 3)
- Sort A1:Bn using B as the sort key
- Delete the data from B:B
The Basic Formula
- Name the last cell of the source "BottomSourceCell"
- Name the first target cell "FirstTargetCell"
Multiple Sources and Targets
Separate Lists, Different Sheets
- Put the lists on separate worksheets
- Put the "BottomSourceCall" Name on on the bottom cell of each list, scoping it to the Worksheet.
- Assuming that each list will be using in a separate worksheet, the formula looks like this
One Worksheet, All Lists
- Put the lists on a single worksheet, naming it something like "Refs" for reference lists.
- Put the "BottomSourceCall" Name at the bottom cell of each list, with a meaning suffix, e.g. ."BottomSourceCall_Month"
- The formula looks like this