Formula that changes the sheet reference AND the cell being referenced

To achieve this, you can use the INDIRECT function in combination with ADDRESS or INDEX to dynamically reference the correct cell based on the dropdown selection and the relative position of the formula as you drag it across rows and columns.

Here's how you can do it:

Step-by-Step Solution:

1. Use the INDIRECT function to dynamically reference the sheet based on the value in cell B5.
2. Use ADDRESS to dynamically create the cell reference as you drag the formula across columns and rows.
3. Combine the sheet name and the cell address using INDIRECT.

Formula:

If your dropdown is in B5 and the cell reference you want to start with is U18, use this formula:

=INDIRECT("'" & $B$5 & "'!" & ADDRESS(ROW($U$18), COLUMN(U18)))

Explanation:

1. $B$5: This is the cell containing the sheet name (e.g., "Sheet A").
2. ROW($U$18): This returns the row number of the reference cell (18 in this case). The $ before U18 makes the row reference absolute, so it doesn’t change as you drag the formula across columns.
3. COLUMN(U18): This returns the column number of the reference cell (U in this case). The column reference is relative (no $ before U), so it changes as you drag the formula across columns.
4. ADDRESS(ROW($U$18), COLUMN(U18)): This creates the cell reference as a text string (e.g., "U18", "V18", etc.) based on the current row and column.
5. INDIRECT("'" & $B$5 & "'!" & ADDRESS(...)): Combines the sheet name from B5 with the dynamically generated cell reference to fetch the value from the correct cell in the correct sheet.

Example:

• If B5 is "Sheet A" and the formula is in the cell that corresponds to U18 in "Sheet A", it will return the value from SheetA!U18.
• If you drag the formula to the right, it will return values from SheetA!V18, SheetA!W18, etc.
• If you drag it down, it will adjust for the row, returning values like SheetA!U19, SheetA!U20, etc.

This approach allows you to dynamically reference both the sheet and the specific cell without hard-coding the cell references, making your summary sheet much more flexible and easier to manage as your workbook changes.