The Problem
Last 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.- A1=M150
- A2=M149
- A3=M148
- etc
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.
Procedural Answer
If someone had to do this once and not worry about extending the data, then the data or formulas can be populated with the following steps
- 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
Formula Answers
The Basic Formula
Many people prefer using formulas they can drag and drop or re-use in some fashion. The key to this formula is to use the INDIRECT function and sync the offsets from top of the target and the bottom of the source. My first cut at this was this, because I think more in the A1-mode than R1C1, then simply copied it down the target column
A1=INDIRECT("M"&150-(ROW()-ROW(A$1)))
NOTE ADDED: Noticed in the published answer the "ROW(A$1)" was not included. In this case, or any when the target starts at row 1 (or if adapting this to columns, the target starts in column A), the anchor is not needed. However, in my opinion, it's a good habit to always include the anchor, just in case the target range changes thanks to a row/column insert or delete.
Greater Re-usability
Thinking further about re-usability of the function, a few iterations later I came up with the following, using R1C1 style:
- Name the last cell of the source "BottomSourceCell"
- Name the first target cell "FirstTargetCell"
- FirstTargetCell=INDIRECT("R"&ROW(BottomSourceCell)-(ROW()-ROW(FirstTargetCell))&"C"&COLUMN(BottomSourceCell),FALSE)
Multiple Sources and Targets
Thinking a little more about it, it is possible that a single workbook would need this multiple times using different lists for each. Suppose a workbook had several lists of information (People, Organizations, Activities, Months, and Days) and the lists needed to be reversed for each. There are a couple of options for solving this issue
Separate Lists, Different Sheets
This approach assumes each list should be on a separate worksheet. This makes sense when there is additonal data along the row.
- 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
=INDIRECT("People!R"&ROW(People!BottomSourceCell)-(ROW()-ROW(TopTargetRow))& "C"&COLUMN(People!BottomSourceCell),FALSE)
One Worksheet, All Lists
This approach assumes little additional data per row, or if there is additional data it is limited, or that the lists won't grow. For example, the list may be the names Months of the years and additional data might be the abbreviation. This is far different than the number of columns that might be appropriate for People, and that the list might grow or shrink. Therefore, some lists might be housed on the same worksheet, perhaps Month information in the first 3 columns and Days of the Week in columns to the right of the Months.
- 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
=INDIRECT("Refs!R"&ROW(BottomSourceCall_Month-(ROW()-ROW(TopTargetRow))& "C"&COLUMN(BottomSourceCall_Month),FALSE)
VBA Answers
Sub ReverseFill()
Const DefaultSourceRangeName As String = "ReverseFillSource"
Dim srcNm As Name
Dim Start As Range
Set Start = ActiveCell
Dim Srce As Range
Dim BottomCell As Range
Dim i As Double
Dim MsgReply As Integer
For Each srcNm In ActiveWorkbook.Names
If LCase(srcNm.Name) = LCase(DefaultSourceRangeName) Then
'Set Srce = ThisWorkbook.Names(DefaultSourceRangeName).RefersToRange
Set Srce = Range(ThisWorkbook.Names(DefaultSourceRangeName).RefersToLocal)
Exit For
End If
Next srcNm
If Srce Is Nothing Then
Do
InputRange = InputBox("Enter the source range by name or in A1 format")
If StrPtr(InputRange) = 0 Then GoTo Exit_ReverseFill ' Checks for Cancel button
For Each srcNm In ActiveWorkbook.Names
If LCase(srcNm.Name) = LCase(InputRange) Then
' Set Srce = ThisWorkbook.Names(InputRange).RefersToLocal
Set Srce = Range(ThisWorkbook.Names(InputRange).RefersToLocal)
Exit For
End If
Next srcNm
If Srce Is Nothing Then
On Error Resume Next
'Set Srce = InputRange.Parent.Name & "!" & ActiveSheet.Range(InputRange)
Set Srce = Range(InputRange)
On Error GoTo 0
End If
If Srce Is Nothing Then
MsgReply = MsgBox("That is not a valid range in A1 form or a valid Named range in the workbook. Try again?", vbYesNo)
InputRange = ""
If MsgReply = vbNo Then GoTo Exit_ReverseFill
End If
Loop Until Not Srce Is Nothing Or StrPtr(InputRange) = 0
End If
If Not Srce Is Nothing Then
Set BottomCell = Srce.Cells(Srce.Rows.Count)
' Set BottomCell = ActiveSheet.Cells(Srce.Rows.Count + Srce.Row - 1, Srce.Column)
For i = 0 To Srce.Rows.Count - 1
Start.Offset(i, 0).Formula = "=" & IIf(ActiveSheet.Name <> Srce.Parent.Name, Srce.Parent.Name & "!", "") & "R" & BottomCell.Row - i & "C" & Srce.Column
'Start.Offset(i, 0) = BottomCell.Offset(-i, 0)
Next i
End If
Exit_ReverseFill:
On Error GoTo 0
End Sub
No comments:
Post a Comment