创建日历：如何通过公式实现跨工作表间隔4行的连续单元格引用？

Absolutely, there's a clean, scalable way to automate this without manually typing each Sheet1!$A$X reference. Here are two reliable methods tailored to your needs:

Method 1: Use the OFFSET Function (Intuitive Offset Calculation) #

This method calculates the target cell by offsetting from your fixed starting point (Sheet1!$A$3) by 4 rows each time:

• Let’s say your first calendar entry is in cell B2. Enter =Sheet1!$A$3 here to set the base reference.
• For the next entry (e.g., cell B3), use this formula:

  =OFFSET(Sheet1!$A$3, 4*(ROW()-ROW($B$2)), 0)
  

• How it works:
  • ROW()-ROW($B$2) gives the number of entries away from your first cell (0 for B2, 1 for B3, 2 for B4, etc.).
  • Multiply that by 4 to get the total rows to shift down from Sheet1!$A$3.
  • Drag this formula down your calendar column, and it’ll automatically reference Sheet1!$A$7, Sheet1!$A$11, and so on.

Method 2: Use the INDIRECT Function (Build Cell References as Text) #

If you prefer constructing cell addresses directly, this method creates the target reference as text then converts it to a live cell link:

• Start with =Sheet1!$A$3 in your first calendar cell (B2).
• For the next cell (B3), use:

  =INDIRECT("Sheet1!$A$" & 3 + 4*(ROW()-ROW($B$2)))
  

• How it works:
  • We start with the base row number (3) and add 4*(ROW()-ROW($B$2)) to calculate each subsequent row (3+4=7, 7+4=11, etc.).
  • The INDIRECT function takes the text string we built (like "Sheet1!$A$7") and turns it into a working cell reference.

Quick Adjustment for Column-Based Calendars #

If your calendar entries are arranged in columns instead of rows, just replace ROW() with COLUMN() in either formula. For example:

=OFFSET(Sheet1!$A$3, 4*(COLUMN()-COLUMN($B$2)), 0)

Pro tip: Avoid referencing the previous calendar cell directly (like =OFFSET(B2,4,0)), as that would shift from the value in B2’s source cell, not maintain the 4-row offset from your original starting point.

内容的提问来源于stack exchange，提问作者Jack Karrde