Skip to content
HOW TO

Create a Dynamic Receiving Range

Goal

Receive variable-size data without overwriting adjacent worksheet content.

Prerequisites

  • A variable-size array, report, or cache key.
  • A reserved output area with no unrelated cells inside its maximum footprint.

Steps

1. Reserve a clear output area and put a visible label or border around it.

2. Return the current array through U.PAD when the output must expand and contract.

=U.PAD(C5,TRUE)

3. Use U.PASTE with an explicit target when the workflow must clear prior values or preserve a controlled destination.

Result

The receiving area expands and contracts with the current data while keeping stale values and adjacent content under control.

Common Variations

  • Use a spill result for read-only output in a clear area.
  • Use U.PAD when a stable footprint or format propagation is needed.
  • Use U.PASTE and an explicit target when old rows must be cleared as data contracts.

Troubleshooting

  • A #SPILL! result means another cell or merged range blocks the output.
  • Stale rows indicate a paste workflow is not clearing the previous footprint.
  • An undersized target can truncate or overwrite data; review its maximum rows and columns.