How not to fill filtered out cells when dragging down from another sheet

Steps:

1. Apply your filter in Sheet1.

2. Select the range where you want to paste the formula (e.g., A2:A100) but only visible cells will be selected.

   • Select the range.

   • Press F5 or Ctrl+G → Click Special… → Choose Visible cells only → OK.

3. Type your formula (e.g.):

   =INDEX(Sheet2!A:A, ROW()-1)

4. Press Ctrl+Enter (instead of just Enter).